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)
)
GOCREATE 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.