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

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
1 year 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
 
 
  • Sandy2024's avatar
    Sandy2024
    New Contributor
    1 year ago

    Hi Spalomino

    Thanks for providing this solution. I have a question, where i would see the results after running this business, Rule?

    Thank you 

    Sandy

     

  • jayaleck's avatar
    jayaleck
    New Contributor III
    2 months 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