The OneStream Community is temporarily frozen until June 29th due to the ongoing maintenance. Please read the blog post here to learn more.
Forum Discussion
Solved
Check details for disk space
Hi guys! I would like to know if there is a way to get a more detailed view of what is consuming disk space for each application within my environment. I understand that the System Diagnostics da...
Are you looking for Breakdown per database table? If so, this is also within System Diagnostics, there is a database table sizes report that can be ran under "Application Analysis" that will show the size of each table within the application database.
You will need to take a snapshot of your application and then following the snapshot creation you can go to the reports tab and choose "Database Table Sizes"
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