Hey David, have the same question, created this, not sure if it's what your looking for. Hope this helps!
Namespace OneStream.BusinessRule.Extender.TEST_dbStats
Public Class MainClass
Public Function Main(ByVal si As SessionInfo, ByVal globals As BRGlobals, ByVal api As Object, ByVal args As ExtenderArgs) As Object
Try
Dim AppName As String = si.AppName
BRApi.ErrorLog.LogMessage(si, "SQL Version: v6 - single real-time query, no OSD dependency")
' ---------------------------------------------------------------
' Database Space Breakdown
' ---------------------------------------------------------------
' TotalSizeGB = ReservedGB + UnallocatedGB + LogFileGB
' ReservedGB = UsedGB + UnusedGB
' UsedGB = DataGB + IndexSizeGB
'
' Definitions:
' TotalSizeGB - Full file size on disk (data files + log file)
' UnallocatedGB - Free space no object has claimed yet
' ReservedGB - Space claimed by tables and indexes
' UsedGB - Space actively holding data and indexes
' UnusedGB - Reserved by objects but sitting empty
' DataGB - Row data only (index_id 0 or 1 = heap/clustered)
' IndexSizeGB - Non-clustered indexes only (index_id > 1)
' LogFileGB - Derived: TotalSize - Reserved - Unallocated
'
' Sources:
' TotalSizeGB, UnallocatedGB → sys.database_files (real-time)
' ReservedGB, DataGB,
' IndexSizeGB, UnusedGB → sys.allocation_units
' + sys.partitions (real-time)
' split on index_id
' ---------------------------------------------------------------
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"
Dim dbConnApp As DbConnInfoApp = BRApi.Database.CreateApplicationDbConnInfo(si)
Dim totalSize As Decimal = 0
Dim usedByFiles As Decimal = 0
Dim unalloc As Decimal = 0
Dim reserved As Decimal = 0
Dim data As Decimal = 0
Dim indexSize As Decimal = 0
Dim unused As Decimal = 0
Dim dbName As String = "N/A"
Using dbConnApp
Using dt As DataTable = BRApi.Database.ExecuteSql(dbConnApp, sql, True)
If dt IsNot Nothing AndAlso dt.Rows.Count > 0 Then
Dim dr As DataRow = dt.Rows(0)
dbName = dr("DatabaseName").ToString()
totalSize = CDec(dr("TotalSizeGB"))
usedByFiles = CDec(dr("UsedByFilesGB"))
unalloc = CDec(dr("UnallocatedGB"))
reserved = CDec(dr("ReservedGB"))
data = CDec(dr("DataGB"))
indexSize = CDec(dr("IndexSizeGB"))
unused = CDec(dr("UnusedGB"))
End If
End Using
End Using
' ---------------------------------------------------------------
' Derive UsedGB and LogFileGB in VB
' ---------------------------------------------------------------
Dim usedGB As Decimal = Math.Round(data + indexSize, 2)
Dim logFileGB As Decimal = Math.Round(totalSize - reserved - unalloc, 2)
' ---------------------------------------------------------------
' Percentage breakdown — against TotalSizeGB as baseline
' ---------------------------------------------------------------
Dim pctReserved As Decimal = Math.Round(reserved / totalSize * 100, 1)
Dim pctUsed As Decimal = Math.Round(usedGB / totalSize * 100, 1)
Dim pctData As Decimal = Math.Round(data / totalSize * 100, 1)
Dim pctIndex As Decimal = Math.Round(indexSize / totalSize * 100, 1)
Dim pctUnused As Decimal = Math.Round(unused / totalSize * 100, 1)
Dim pctUnalloc As Decimal = Math.Round(unalloc / totalSize * 100, 1)
Dim pctLog As Decimal = Math.Round(logFileGB / totalSize * 100, 1)
' ---------------------------------------------------------------
' Math verification checks
' ---------------------------------------------------------------
Dim checkTotal As Decimal = Math.Round(reserved + unalloc + logFileGB, 2)
Dim checkReserved As Decimal = Math.Round(usedGB + unused, 2)
Dim checkUsed As Decimal = Math.Round(data + indexSize, 2)
Dim totalRounded As Decimal = Math.Round(totalSize, 2)
Dim reservedRounded As Decimal = Math.Round(reserved, 2)
' ---------------------------------------------------------------
' Log full breakdown
' ---------------------------------------------------------------
BRApi.ErrorLog.LogMessage(si,
$"App: {AppName} | DB: {dbName} | " &
$"--- Level 1 (Total) --- " &
$"TotalSize: {totalSize} GB | " &
$"UsedByFiles: {usedByFiles} GB | " &
$"Unallocated: {unalloc} GB ({pctUnalloc}% of Total) | " &
$"LogFile: {logFileGB} GB ({pctLog}% of Total) | " &
$"--- Level 2 (Reserved) --- " &
$"Reserved: {reserved} GB ({pctReserved}% of Total) | " &
$"UsedGB (Data+Index): {usedGB} GB ({pctUsed}% of Total) | " &
$"Unused: {unused} GB ({pctUnused}% of Total) | " &
$"--- Level 3 (Used) --- " &
$"Data: {data} GB ({pctData}% of Total) | " &
$"IndexSize: {indexSize} GB ({pctIndex}% of Total) | " &
$"--- Math Checks --- " &
$"Total=Reserved+Unalloc+Log: {checkTotal} = {totalRounded} → {checkTotal = totalRounded} | " &
$"Reserved=Used+Unused: {checkReserved} = {reservedRounded} → {checkReserved = reservedRounded} | " &
$"Used=Data+Index: {checkUsed} = {usedGB} → {checkUsed = usedGB}"
)
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
