SQL Query that gives you the spaced used by Diskgroups?

[Renoldo__Keith_]

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.

[lboening]

If you knew where your files were located on the file server file system, you could find out exactly how much file space they take.

gallery.technet.microsoft.com/.../Get-FolderSize-b3d317f5

[lboening]

Or, using a SQL statement

SELECT SUM(FILESIZE) FROM ITEMDATAPAGE;

The above is Oracle Dialect

[Walter_Fernande]

Hello Renoldo, try this SQL Query

SELECT DG.[diskgroupnum],[diskgroupname], SUM(filesize)/(1024*1024) as [Space in MB]
FROM [hsi].[diskgroup] DG,[hsi].itemdatapage IDP
where DG.diskgroupnum=IDP.diskgroupnum
group by DG.[diskgroupnum],[diskgroupname]

Hope that it helps.

[Alex_French · Accepted solution]

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.