Systems Diagnostics - Database Sizes math?
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 =
"SELECT " &
" DB_NAME() AS DatabaseName, " &
" CAST(f.total_size * 8.0 / 1048576 AS DECIMAL(10,2)) AS TotalSizeGB, " &
" CAST(f.total_used * 8.0 / 1048576 AS DECIMAL(10,2)) AS UsedByFilesGB, " &
" CAST((f.total_size - f.total_used) " &
" * 8.0 / 1048576 AS DECIMAL(10,2)) AS UnallocatedGB, " &
" CAST(s.reserved_pages * 8.0 / 1048576 AS DECIMAL(10,2)) AS ReservedGB, " &
" CAST(s.data_pages * 8.0 / 1048576 AS DECIMAL(10,2)) AS DataGB, " &
" CAST(s.index_pages * 8.0 / 1048576 AS DECIMAL(10,2)) AS IndexSizeGB, " &
" CAST((s.reserved_pages - s.data_pages - s.index_pages) " &
" * 8.0 / 1048576 AS DECIMAL(10,2)) AS UnusedGB " &
"FROM ( " &
" SELECT " &
" SUM(size) AS total_size, " &
" SUM(FILEPROPERTY(name, 'SpaceUsed')) AS total_used " &
" FROM sys.database_files " &
") f " &
"CROSS JOIN ( " &
" SELECT " &
" SUM(a.total_pages) AS reserved_pages, " &
" SUM(CASE WHEN p.index_id IN (0,1) " &
" THEN a.used_pages " &
" ELSE 0 END) AS data_pages, " &
" SUM(CASE WHEN p.index_id > 1 " &
" THEN a.used_pages " &
" ELSE 0 END) AS index_pages " &
" FROM sys.partitions p " &
" JOIN sys.allocation_units a " &
" ON p.partition_id = a.container_id " &
") 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