Hyland Connect

Renoldo__Keith_ · ‎04-12-2017

I am still new to Transactional SQL and was wondering if anyone has written an SQL Query that gives you the current spaced used by your Disk Groups? I've looked at Report Services but the pre-packaged reports for Disk Groups does not have what I'm looking for. Thanks in advance for anyone's assistance on this.

Alex_French · ‎04-12-2017

Hi Renoldo,

I think this is roughly what you're looking for, giving both counts and sizes-

set transaction isolation level read uncommitted;

select
hsi.diskgroup.diskgroupname,
count(filesize) filecount,
sum(cast(filesize as bigint))/ 1000000000 sizeGBs,
sum(cast(filesize as bigint))/ 1000000 sizeMBs
from hsi.itemdatapage
left join hsi.diskgroup
on hsi.itemdatapage.diskgroupnum = hsi.diskgroup.diskgroupnum
group by hsi.diskgroup.diskgroupname;

UPDATE: I added the setting of the transaction level based on Ansley's point below, and as she points out the count() is not grouping by document id, so the count represents individual files on disk, not separate OnBase documents.

View answer in original post

Ansley_Ingram · ‎04-12-2017

Hi everyone,

Please do not run any of these suggested queries against your production database.

The itemdatapage table is generally one of the largest tables in the OnBase database and the information you are looking for is not typically indexed. Running these queries in a production environment could have a significant performance impact on your solution.

Renoldo,

The most accurate way to determine the space used in the disk groups would be to look at this from the OS perspective, not from the database perspective.

If you must do this via SQL - you would need to take into account the fact that different file types can be represented differently in hsi.itemdatapage - multipage TIFs are actually listed multiple times - once for each page in the file, whereas files that contain multiple documents (such as COLD reports) are also listed multiple times. The stock reports in Report Services do take this into account and can be utilized as templates for other reporting needs.

Given the performance impact that these type of queries can have on your system, I'd highly recommend working with your DBAs on this, since you mentioned that you are new to working with SQL.

Hope that helps,

Ansley

Hyland Connect

SQL Query that gives you the spaced used by Diskgroups?