Forum Discussion

Beeswing's avatar
Beeswing
New Contributor III
4 years ago

How to check disk space

Hi, I've had a dig around, but I can't seem to find it.  Anyone know where I can see the disk space utilisation? We've set up a number of new apps in our environment recently, and I'm concerned ...
Infrastructure
Spalomino's avatar
Spalomino
New Contributor III
10 months ago

Mark:  I hope you don't mind.  I add the full code.  For those like me who struggle with VB.  :)

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
 
Namespace OneStream.BusinessRule.Extender.SJP_GIGChk 'Your Extensibility Rule must be the same name.
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
'Hi, you can check an individual database size With an extensibility rule containing code Like this:
 
Dim sql As String = "EXEC sp_spaceused;"
Dim dbConnApp As DbConnInfoApp = brapi.Database.CreateApplicationDbConnInfo(si)
 
Using dbConnApp
Using dt As DataTable = BRAPi.Database.ExecuteSql(dbConnApp, sql.ToString, True)
If Not dt Is Nothing Then
'Reference dt to access data table results
For Each dr As DataRow In dt.Rows
'Process rows
brapi.ErrorLog.LogMessage(si, "database_size: " + dr("database_size"))
brapi.ErrorLog.LogMessage(si, "unallocated space: " + dr("unallocated space"))
Next
 
End If
End Using
End Using
 
Return Nothing
Catch ex As Exception
Throw ErrorHandler.LogWrite(si, New XFException(si, ex))
End Try
End Function
End Class
End Namespace
 
 
jayaleck's avatar
jayaleck
New Contributor III
2 days ago

Hope you don't mind, took what you did and ran with it!

Imports System
Imports System.Collections.Generic
Imports System.Data
Imports System.Data.Common
Imports System.Globalization
Imports System.IO
Imports System.Linq
Imports Microsoft.VisualBasic
Imports OneStream.Finance.Database
Imports OneStream.Finance.Engine
Imports OneStream.Shared.Common
Imports OneStream.Shared.Database
Imports OneStream.Shared.Engine
Imports OneStream.Shared.Wcf
Imports OneStream.Stage.Database
Imports OneStream.Stage.Engine
 
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