Improved Drive Space Monitoring

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

Step 1

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

Step 2

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'

Step 3

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

Step 4

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

Step 5

Erase Old Drive Info File. This just deletes the drive info file for the next run, it is an Operating system (CmdExec) command.

Erase c:\Monitor_Capture_Drive_Space_Info.csv

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.