Several years ago I submitted the article ‘Monitoring Drive and Database Free Space’,
which described a method we had developed to monitor and report on space usage at the drive and database level. Since then we have worked to improve and adapt it for implementation on SQL 2005 Servers.
This article describes changes made to the drive monitoring aspect of this process. The changes are simple, 1) eliminate ‘xp_fixeddrives’ and 2) use SQL 2005 ‘Database Mail’ for email.
I will present this process as a 2005 SQL Server Agent Job. Job execution requires that,
1) ‘xp_cmdshell’ and ‘Database Mail XP’s’ be enabled and 2) a ‘Database Mail Account’ and ‘Database Mail Profile’ be created
The basic job steps are:
- Step 1 – Create DrvSpace Table.
- Step 2 – Get Drive Space Data.
- Step 3 – Bulk Insert into DrvSpace
- Step 4 – MonitorEmailLowDriveFreeSpace.
- Step 5 – Erase Old Drive Info File
Create DrvSpace Table. This step simply creates an empty table ‘DrvSpace’ in tempdb for each run.
use tempdb go if exists (select name from tempdb..sysobjects where name = 'DrvSpace' and type = 'U') begin drop table DrvSpace end Create table DrvSpace ( DriveLetter char(02) null, MB_Total int null, MB_Free int null ) Go
Get Drive Space Data. This is the first part of replacing ‘xp_fixeddrives’. It’s a VB Script that gathers space information on local drives and writes that information into local file ‘c:\Monitor_Capture_Drive_Space_Info.csv’.
EXECUTE master.dbo.xp_cmdShell 'cscript.exe \\LocalServerName\c$\Monitor_Capture_Drive_Space_Info.vbs'
Bulk Insert into DrvSpace. This is the second part of replacing ‘xp_fixeddrives’. This Bulk Insert gets the local drive information into tempdb..DrvSpace.
use tempdb go Bulk Insert DrvSpace from 'C:\Monitor_Capture_Drive_Space_Info.csv' with ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ) Go
MonitorEmailLowDriveFreeSpace. This step executes the Stored Procedure sp_Monitor_Email_Low_Drive_Free_Space’. As executed here, it will look for local drives with under 10% (0.1) free space. Email notifications are sent, identifying these drives. This procedure is actually more flexible than displayed here, the C: Drive is handled separately with a 15% free space limit and any other Drive can be specified as an ‘exception’, with it’s own specific free space limit. Documentation embedded in this Stored Procedure details its operation. Note that this procedure must be edited to reflect a valid ‘Database Mail Profile’ (XXXXXX DBMail Profile) and email address (XXXX@YY.ZZ).
EXEC msdb.dbo.sp_Monitor_Email_Low_Drive_FreeSpace 0.1
Erase Old Drive Info File. This just deletes the drive info file for the next run, it is an Operating system (CmdExec) command.
That's it and please feel free to comment or ask questions in the article discussion. The code is attached in the resource section below.