THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Merrill Aldrich

POSH-y SQL Disk Space Monitoring

In a prior post I expressed my dismay that apparently Operations Manager (which I dig in other respects) has no love for SQL Server storage that uses mount points. Herewith, henceforth, forthwith is a PowerShell workaround I am using until I find out I am wrong, or there’s a management pack fix. The crux of the issue, I think, is that SQL Server itself has basically no visibility to the disks mounted using mount points, and that blind spot carries through to the management pack. That much is well trodden ground on blogs and forums, going back to 2005, with some good solutions using CLR (or previously sp_OACreate), like:

http://weblogs.sqlteam.com/tarad/archive/2007/12/18/60435.aspx 

There are also plenty of articles about how to look at the Windows admin / OS side of the equation with PowerShell and WMI. I read many of these articles and sort of mashed them together here into a single PowerShell solution. I read so many, my apologies for not crediting each author here. I wanted a simple PowerShell solution that would not only show the available space on each actual disk, but also the size and growth implications for every SQL Server file, for every database, to quickly answer questions like “will the next autogrow work for all files on this cluster?” or “How much can this particular database grow, including space inside the files and space outside the files?” The biggest issue is to gather the real disk information from WMI and correlate it to all the file paths in SQL Server.

Here’s a solution – first, I have a utility database on each instance called “DBAdmin_<SQLServerName>[_<InstanceName>].” This holds various monitoring tables and procedures. To this database I added a couple of tables to track disk information from the operating system and file information from SQL Server:

CREATE TABLE dbo.TrackDiskSpace (
     AsOfDate datetime,
     DeviceID nvarchar(128),
     Caption nvarchar(1024),
     Label nvarchar(1024),
     CapacityMB numeric(18, 4),
     FreeSpaceMB numeric(18, 4)
    )
GO
CREATE TABLE dbo.TrackFileInfo(
    DBName sysname,
    Physical_Name nvarchar(260) NOT NULL,
    [Type] tinyint NOT NULL,
    Type_Desc nvarchar(60) NULL,
    [State] tinyint NULL,
    SizeMB int NULL,
    Max_SizeMB numeric(18, 4) NULL,
    Growth numeric(18, 4) NULL,
    NextGrowthIncMB numeric(18, 4) NULL,
    SpaceUsedMB numeric(18, 4) NULL,
    Is_Autogrow int NOT NULL,
    Is_Read_Only bit NOT NULL,
    Is_Percent_Growth bit NOT NULL,
    AsOfDate datetime NULL
) 
GO

These tables are, not coincidentally, very similar to the properties of the WMI disk volume object and the SQL Server DMV sys.database_files. I added a couple of derived columns and a date stamp for convenience and to enable sampling these values over time.

Next, I created a SQL query to gather the information for all online database files across a whole instance, which is available in many forms out there on teh Intertubez. I tweaked it a little to get the values into units that are more familiar (MB) and present some other values in a friendly format. This just involves walking the databases and selecting from sys.sysdatabase_files:

USE master

CREATE TABLE #fileInfo (
    dbname sysname,
    physical_name nvarchar(260),
    type tinyint,
    type_desc nvarchar(60),
    state tinyint,
    sizeMB numeric(18,2),
    max_sizeMB numeric(18,2),
    growth numeric(18,2),
    nextGrowthIncMB numeric(18,2),
    SpaceUsedMB numeric(18,2),
    is_autogrow bit,
    is_read_only bit,
    is_percent_growth bit
   )

DECLARE dblist CURSOR FAST_FORWARD FOR
    SELECT  name
    FROM    sys.databases
    WHERE state_desc = 'ONLINE'     

DECLARE @dbname sysname
DECLARE @fileQuery nvarchar(max)

OPEN dblist
FETCH NEXT FROM dblist INTO @dbname
WHILE @@FETCH_STATUS = 0 
BEGIN

    SET @fileQuery = '
    USE ' + QUOTENAME(@dbname) + '
    INSERT INTO #fileInfo
    ( dbname,
     physical_name,
      type,
      type_desc,
      state,
      sizeMB,
      max_sizeMB,
      growth,
      nextGrowthIncMB,
      SpaceUsedMB,
      is_autogrow,
      is_read_only,
      is_percent_growth
    ) SELECT  db_name() dbname,
           physical_name,
            type,
            type_desc,
            state,
            ( size / 128 ) as sizeMB,
            case when max_size = -1 then -1 
                when type = 1 and max_size = 268435456 then -1
                else ( max_size / 128.0) end as max_sizeMB,
            case is_percent_growth when 0 then ( growth / 128.0 ) 
                when 1 then growth * 1.0 
                else null end as growth,            
            case is_percent_growth when 0 then ( growth / 128.0 ) 
                when 1 then ( ( growth / 100.0 ) * size ) / 128.0 
                else null end as nextGrowthIncMB,
            FILEPROPERTY( name, ''SpaceUsed'' ) / 128.0 AS SpaceUsedMB,
            case growth when 0 then 0 else 1 end as is_autogrow,
            is_read_only,
            is_percent_growth
        FROM sys.database_files'

    EXEC ( @fileQuery )
    FETCH NEXT FROM dblist INTO @dbname
END
CLOSE dblist
DEALLOCATE dblist

SELECT * FROM #fileInfo

DROP TABLE #fileInfo

That would be peachy all on it’s own, were it not for this visibility problem for mount points. To get at the root monitoring issue, “are we going to have a space problem,” we have to correlate all this file information with the real disks where the files actually live, plus factor in settings like autogrow, growth increment, space inside the files, space outside the files, etc.

Here a PowerShell script fits the bill – we can run a single script that captures all the OS information about the system’s disks using WMI, and also executes the above query at (approximately) the same moment, storing the results in the tables listed above. Note that the long SQL query embedded below should be identical to the listing above:

param( $serverName = $(throw 'Parameter $serverName is required'), `
  $sqlServerName = $(throw 'Parameter $sqlServerName is required'), `
  $rootDisk = $(throw 'Parameter $rootDisk is required')
)

$adminDBName = 'DBAdmin_' + ( $sqlServerName -replace '\\', '_' )
$scriptTime = (get-date)

$diskSpaceTable = 'dbo.TrackDiskSpace'
$fileInfoTable = 'dbo.TrackFileInfo'

# Make a data table to store OS disk information 

$diskSpaceDT = New-Object System.Data.DataTable

$asOfCol = New-Object System.Data.DataColumn 'AsOfDate', ([DateTime]) 
$asOfCol.DefaultValue = $scriptTime
$diskSpaceDT.Columns.Add( $asOfCol )

$diskSpaceDT.Columns.Add( ( New-Object System.Data.DataColumn 'DeviceID', ([string]) ) )
$diskSpaceDT.Columns.Add( ( New-Object System.Data.DataColumn 'Caption', ([string]) ) )
$diskSpaceDT.Columns.Add( ( New-Object System.Data.DataColumn 'Label', ([string]) ) )
$diskSpaceDT.Columns.Add( ( New-Object System.Data.DataColumn 'CapacityMB', ([double]) ) )
$diskSpaceDT.Columns.Add( ( New-Object System.Data.DataColumn 'FreeSpaceMB', ([double]) ) )

# Gather the disk information from the server
Get-WmiObject -computername "$serverName" win32_volume -filter "DriveType = 3" `
  | Where-Object { $_.Caption -like "$rootDisk*" } `
  | foreach {

      $newRow = $diskSpaceDT.NewRow()
      $newRow["DeviceID"] = $_.DeviceID
      $newRow["Caption"] = $_.Caption
      $newRow["Label"] = $_.Label
      $newRow["CapacityMB"] = [math]::round( $_.Capacity / 1MB, 2 )
      $newRow["FreeSpaceMB"] = [math]::round( $_.FreeSpace / 1MB, 2 )
      $diskSpaceDT.Rows.Add( $newRow )

   }

# $diskSpaceDT | Format-List 

# Compose a SQL command to get file information for all online databases / all files

$sqlCon = New-Object System.Data.SqlClient.SqlConnection
$sqlCon.ConnectionString = "Server=$sqlServerName;Database=master;Integrated Security=True"

$sqlCmd = New-Object System.Data.SqlClient.SqlCommand
$sqlCmd.Connection = $sqlCon
$sqlCmd.CommandText = @"

USE master

CREATE TABLE #fileInfo (
    dbname sysname,
    physical_name nvarchar(260),
   … < body of the SQL file information query above goes here > …
 
DEALLOCATE dblist

SELECT * FROM #fileInfo

DROP TABLE #fileInfo

"@

# Populate the data table using the file info query 

$fileInfoDT = New-Object System.Data.DataTable

$sqlCon.Open()
$sqlReader = $sqlCmd.ExecuteReader()
$fileInfoDT.Load($SqlReader)
$sqlCon.Close()

$asOfCol = New-Object System.Data.DataColumn 'AsOfDate', ([DateTime]) 
$asOfCol.DefaultValue = $scriptTime

$fileInfoDT.Columns.Add( $asOfCol )

# $fileInfoDT | Format-List

# Write the results out to the admin database
$destCon = "Data Source=$sqlServerName;Initial Catalog=$adminDBName;Integrated Security=True;"

$bulkCopier = New-Object Data.SqlClient.SqlBulkCopy $destCon
$bulkCopier.DestinationTableName = $diskSpaceTable
$bulkCopier.WriteToServer( $diskSpaceDT )
$bulkCopier.DestinationTableName = $fileInfoTable
$bulkCopier.WriteToServer( $fileInfoDT )

We pass in the windows server name (for a cluster instance, that would be the cluster virtual name), the SQL Server name (identical save for named instances) and the drive letter representing the root disk where the SQL Server files are for the instance. For example, a disk E: might be the root of a cluster virtual SQL instance, and have mount points for other physical disks as E:\SQLData and E:\SQLLog. In that case, pass ‘E’.

First, we use the win32_volume object to get the capacity information for the root disk (E) and all other disks connected to it as mount points. The vital bit about the WMI query is that the win32_volume object contains the path for the mount point as the .Caption property. Later, that will allow us to compute which data and log files are really on which physical disks.

We then run the SQL query to gather all the database and log file information, stamping those rows with the exact same datetime value as the disk information from the first step.

Last, we bulk load the results into the SQL tracking tables. Once the script runs, the tracking tables should have a set of rows describing the disks and a set of rows describing all the SQL Server files for the instance, with matching datetime stamps.

The last trick is to correlate the two sets of data, and compute some basic columns indicating things like “how much can this data file grow” and “am I going to have a problem.” This requires essentially two things: the two tables have to be joined in a vaguely clever way to align which disk actually hosts which file. In the case of our file systems, this can be accomplished by using the length of the value in the disk caption: basically we compare the physical_name of the SQL Files to the disk volume caption, finding the longest disk caption value that matches, by string length. Not terribly elegant, but it works :-). It should work, I think, for most folder structures. That logic is something like:

SELECT  f.AsOfDate,
        f.Physical_Name,
        ( SELECT TOP 1
             Caption
          FROM      dbo.TrackDiskSpace d
          WHERE     f.AsOfDate = d.AsOfDate
             AND f.Physical_Name LIKE d.Caption + '%'
          ORDER BY  LEN(d.Caption) DESC
        ) AS [Disk]
FROM      dbo.TrackFileInfo f

Secondly, we have to fold together the computation about whether autogrow is on, and the files have a max size, and space inside or outside, etc. If files are set to autogrow, then we care whether they have space inside AND space on disk to grow; if not, we care only about the remaining space in the file itself. That calculation boils down to some kind of CASE statement like:

...
CASE 
-- Unlimited file size and autogrow ON: we're limited 
-- only by available OS disk space
         WHEN fi.Is_Autogrow = 1
              AND fi.Max_SizeMB = -1
         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
              + ds.FreeSpaceMB                -- remaining space on the disk
-- Limited file size and autogrow ON: we're limited by the lesser of 
-- available OS file space or max file size
         WHEN fi.Is_Autogrow = 1
              AND fi.Max_SizeMB != -1
         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
              + ( CASE WHEN ( fi.Max_SizeMB - fi.SizeMB ) < ds.FreeSpaceMB
                       THEN ( fi.Max_SizeMB - fi.SizeMB )
                       ELSE ds.FreeSpaceMB
                  END )        -- the lesser of hitting the max sql file size 
                            -- or exceeding remaining os disk space
-- Autogrow OFF: we're limited by the existing file size
         WHEN fi.Is_Autogrow = 0
         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
         ELSE NULL
    END AS NetAvailableSpace
...

For sanity, I folded those two tasks together into a single summary view:

CREATE VIEW TrackDiskSpaceSummary AS

SELECT  AsOfDate,
        DBName,
        [File],
        [Disk],
        DiskCapacityMB,
        DiskFreeSpaceMB,
        Type_Desc,
        SizeMB,
        SpaceUsedMB,
        Max_SizeMB,
        NextGrowthIncMB,
        Growth,
        Is_Autogrow,
        Is_Percent_Growth,
        NetAvailableSpace,
        CASE WHEN SpaceUsedMB > 0
             THEN CAST (
                  ( NetAvailableSpace / SpaceUsedMB ) * 100 
                  AS numeric(18, 2)
                )
             ELSE NULL
        END AS PercentAvailableGrowth,
        CASE WHEN Is_Autogrow = 1 AND NextGrowthIncMB <= DiskFreeSpaceMB 
            THEN 'Yes'
            WHEN Is_Autogrow = 1 AND NextGrowthIncMB > DiskFreeSpaceMB 
            THEN 'No'
            ELSE 'Autogrow Off' 
        END AS NextAutogrowFits
FROM    ( SELECT    fi.AsOfDate,
                    fi.DBName,
                    fi.Physical_Name [File],
                    ds.Caption [Disk],
                    ds.CapacityMB [DiskCapacityMB],
                    ds.FreeSpaceMB [DiskFreeSpaceMB],
                    fi.Type_Desc,
                    fi.SizeMB,
                    fi.SpaceUsedMB,
                    fi.Max_SizeMB,
                    fi.NextGrowthIncMB,
                    fi.Growth,
                    fi.Is_Autogrow,
                    fi.Is_Percent_Growth,
                    CASE 
                -- Unlimited file size and autogrow ON: we're limited 
                -- only by available OS disk space
                         WHEN fi.Is_Autogrow = 1
                              AND fi.Max_SizeMB = -1
                         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
                              + ds.FreeSpaceMB                -- remaining space on the disk
                -- Limited file size and autogrow ON: we're limited by the lesser of 
                -- available OS file space or max file size
                         WHEN fi.Is_Autogrow = 1
                              AND fi.Max_SizeMB != -1
                         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
                              + ( CASE WHEN ( fi.Max_SizeMB - fi.SizeMB ) < ds.FreeSpaceMB
                                       THEN ( fi.Max_SizeMB - fi.SizeMB )
                                       ELSE ds.FreeSpaceMB
                                  END )        -- the lesser of hitting the max sql file size 
                                            -- or exceeding remaining os disk space
                -- Autogrow OFF: we're limited by the existing file size
                         WHEN fi.Is_Autogrow = 0
                         THEN ( fi.SizeMB - fi.SpaceUsedMB )  -- remaining space inside the file
                         ELSE NULL
                    END AS NetAvailableSpace
          FROM      ( 
                    -- Correlate physical file paths to disks by comparing
                    -- OS disk caption to SQL Server physical file path
                    -- and finding the *longest* matching disk caption
                    SELECT    f.AsOfDate,
                                f.Physical_Name,
                                ( SELECT TOP 1
                                     Caption
                                  FROM      dbo.TrackDiskSpace d
                                  WHERE     f.AsOfDate = d.AsOfDate
                                     AND f.Physical_Name LIKE d.Caption + '%'
                                  ORDER BY  LEN(d.Caption) DESC
                                ) AS [Disk]
                      FROM      dbo.TrackFileInfo f
                    ) AS fileDisks
                    INNER JOIN dbo.TrackFileInfo fi 
                      ON fileDisks.Physical_Name = fi.Physical_Name
                        AND fi.AsOfDate = fileDisks.AsOfDate
                    INNER JOIN dbo.TrackDiskSpace ds 
                      ON ds.AsOfDate = fileDisks.AsOfDate
                        AND ds.Caption = fileDisks.[Disk]
          WHERE     fi.AsOfDate = ( SELECT  MAX(AsOfDate)
                                    FROM    dbo.TrackFileInfo
                                  )
        ) AS SpaceComputation
        
GO        

From that view, we can now do something nice and tidy, like:

SELECT  DBName,
        [File],
        NetAvailableSpace,
        PercentAvailableGrowth,
        NextAutogrowFits
FROM    dbo.TrackDiskSpaceSummary
ORDER BY dbname

That yields, provided I got all the arithmetic right, the actual growth/space situation of all files from all databases on all mount points. A query like that can be placed into either a reporting or alerting setup to give you real disk stats across the enterprise.

So, in summary:

  • Mount Points are awesome, if second-class, constructs. If you use clustering, chances are you use mount points too.
  • Some SQL monitoring software can manage this mount point computation, some not.
  • The data describing mount point disks is only available from the OS; the data describing SQL files space usage is only available via a SQL DMV. That means, sadly, some stunt like this to marry the two.
Published Tuesday, January 04, 2011 11:46 PM by merrillaldrich

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Sean said:

This was great information. I used it to put together a solution for my environment.

I removed the rootDisk parameter and the " | Where-Object { $_.Caption -like "$rootDisk*" } `"

I like how this also functions as a history table for disk space usage because of your use of AsOfDate

September 19, 2011 5:20 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement