Systems Diagnostics - Database Sizes math?

Question

Does anyone know how Used (GB) and Unused is derived? Maybe I'm wrong, going to assume it's looking at total_size, total_used, reserved_pages, ect.. Trying to figure out the math, and questioning if it's correct. This is what I have so far, any feedback is appreciated!
Dim sql As String =&nbsp; &nbsp; &nbsp; &nbsp; "SELECT " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;DB_NAME() &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS DatabaseName, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST(f.total_size &nbsp; &nbsp; &nbsp;* 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS TotalSizeGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST(f.total_used &nbsp; &nbsp; &nbsp;* 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS UsedByFilesGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST((f.total_size - f.total_used) " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS UnallocatedGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST(s.reserved_pages &nbsp;* 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS ReservedGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST(s.data_pages &nbsp; &nbsp; &nbsp;* 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS DataGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST(s.index_pages &nbsp; &nbsp; * 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS IndexSizeGB, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;CAST((s.reserved_pages - s.data_pages - s.index_pages) " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; * 8.0 / 1048576 AS DECIMAL(10,2)) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;AS UnusedGB " &amp;&nbsp; &nbsp; &nbsp; &nbsp; "FROM ( " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;SELECT " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp;SUM(size) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS total_size, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp;SUM(FILEPROPERTY(name, 'SpaceUsed')) &nbsp; &nbsp;AS total_used " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;FROM sys.database_files " &amp;&nbsp; &nbsp; &nbsp; &nbsp; ") f " &amp;&nbsp; &nbsp; &nbsp; &nbsp; "CROSS JOIN ( " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;SELECT " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp;SUM(a.total_pages) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS reserved_pages, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp;SUM(CASE WHEN p.index_id IN (0,1) " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN a.used_pages " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 END) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS data_pages, " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp;SUM(CASE WHEN p.index_id &gt; 1 " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; THEN a.used_pages " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; ELSE 0 END) &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; AS index_pages " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;FROM sys.partitions p " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp;JOIN sys.allocation_units a " &amp;&nbsp; &nbsp; &nbsp; &nbsp; " &nbsp; &nbsp; &nbsp;ON p.partition_id = a.container_id " &amp;&nbsp; &nbsp; &nbsp; &nbsp; ") s"
Here's the output
--- Level 1 (Total) --- TotalSize: 1394.27 GB
UsedByFiles: 228.51 GB
Unallocated: 1165.75 GB (83.6% of Total)
LogFile: 1.67 GB (0.1% of Total)
--- Level 2 (Reserved) ---
Reserved: 226.85 GB (16.3% of Total)
UsedGB (Data+Index): 221.60 GB (15.9% of Total)
Unused: 5.25 GB (0.4% of Total)
--- Level 3 (Used) ---
Data: 129.73 GB (9.3% of Total)
IndexSize: 91.87 GB (6.6% of Total)
--- Math Checks ---
Total=Reserved+Unalloc+Log: 1394.27 = 1394.27 → True
Reserved=Used+Unused: 226.85 = 226.85 → True
Used=Data+Index: 221.60 = 221.60 → True

JJones · Answer

OneStream System Diagnostics is pulling the values from the System database or is running a stored procedure depending on if this is Standard SQL Server or SaaS and then divides the values by 1024 to get each value.
It pulls from Sys.ResourceStats for SAAS and runs a stored procedure EXEC sp_spaceused @oneresultset = 1 for Standard SQL which returns the values in KB and then does the math to convert to GB

Forum Discussion

Systems Diagnostics - Database Sizes math?

1 Reply

Related Content

Application Database Folder and System Database Folder

OneStream System Diagnostic - BR OSD_SolutionHelper does not compile

Database Transaction

Database (de)fragmentation

Database information reports