Disk space monitoring with SQL Server

In this post, I discuss 3 methods (of varying complexity) to carry out some disk space monitoring with SQL server. This can be ad-hoc or you can build it into SQL agent jobs to run on a schedule. Now the first thing I’d like to say is this isn’t a replacement for good monitoring software, it’s more of a compliment to monitoring software. It’s a double check in case something goes wrong with your monitoring. Having some handy scripts is useful as well, because it lets you find out info on an ad-hoc basis, rather than relying on alerts or checking manually.

Simple method

[sql]xp_fixeddrives[/sql]

The xp_fixeddrives procedure is a simple check to see what space is free on every drive (in MB). This is fine if that’s the only info you need but it won’t give you the drive size so you can’t see what percentage is free. It’s a start but it’s not great.

 

 

Advanced method (SQL Server 2008 R2 SP1 onwards)

Here, we take advantage of the DMF sys.dm_os_volume_stats and the system-wide view sys.master_files. Using my test server, the query returns Drive, SizeGB, FreeSpaceGB and percent_free for every drive that has a database file on it (data or log).

[sql]

SELECT DISTINCT
volume_mount_point as Drive,
total_bytes/1073741824 as SizeGB,
available_bytes/1073741824 as FreeSpaceGB,
LEFT((CAST(available_bytes as decimal) / CAST(total_bytes as decimal)*100),5)+’%’ as percent_free
FROM sys.master_files smf
CROSS APPLY sys.dm_os_volume_stats(smf.database_id,smf.file_id)
ORDER BY volume_mount_point;

[/sql]

This is much better, except for one major omission. This only returns data for drives which hold data or log files. If the drive is used for operating system only or backups, you will not be able to get this back directly using only T-SQL.

Advanced method (PowerShell)

If you haven’t used PowerShell before, this could be a good place to start. It’s still a fairly simple solution and a good way to get on board with PowerShell. Depending on your execution policy, you might be able to set this up in one remote location and then target all of your SQL Servers. Alternatively, you might need to store the script on each SQL server you wish to monitor. Read more about execution policy.

Part 1

Save the code below in a suitable location and give it the .ps1 extension. e.g. E:\Scripts\PowerShell\DriveSpace\DriveSpace.ps1

[code]

$ExportPath = ‘E:\Scripts\PowerShell\DriveSpace\DriveSpace.csv’
$Disk = Get-WmiObject Win32_LogicalDisk -ComputerName DBTESTSERVER | SELECT DeviceID,FreeSpace,Size | Export-Csv -NoTypeInformation -Path $ExportPath
(get-content $ExportPath) | foreach-object { $_ -replace “”””, “” } | set-content $ExportPath -Force

[/code]

Test this works by right clicking the file and selecting: Run with PowerShell. (If it works, it should have created the file DriveSpace.csv. If not, you might want to check your execution policy).

Part 2

Open SQL Server Management Studio (SSMS) and copy in the code below. Change the file location in the BULK INSERT statement to point at your csv file.

[sql]

CREATE TABLE #DriveSpace
(
DeviceID NVARCHAR(256),
FreeSpace BIGINT,
Size BIGINT
)

GO

BULK INSERT #DriveSpace
FROM ‘E:\Scripts\PowerShell\DriveSpace\DriveSpace.csv’
WITH
(
FIRSTROW=2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)

GO

ALTER TABLE #DriveSpace ADD percent_free AS
(CAST(FreeSpace as decimal)/CAST(Size as decimal) * 100)

GO

SELECT
DeviceID,
Size/1073741824 as SizeGB,
FreeSpace/1073741824 as FreeSpaceGB,
LEFT(percent_free,5)+’%’ as percent_free
FROM #DriveSpace
WHERE percent_free < 5
AND DeviceID NOT LIKE ‘%Q:%’

[/sql]

 

This gives us everything we need now in an easy to read to format. If you set this up as a SQL agent job, you will want to send an email when a certain condition is met. You can use the query in this older post as a guide and change the select query to match the one in the script above.

As always, any feedback is greatly appreciated.