Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

Improved Drive Space Monitoring

By Mark Nash,

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.

 

Resources:

Step 1 - Create DrvSpace Table.sql | Step 2 - Get Drive Space Data.abc | Step 3 - Bulk Insert into DrvSpace.sql | Step 4 - MonitorEmailLowDriveFreeSpace.sql
Total article views: 4677 | Views in the last 30 days: 3
 
Related Articles
ARTICLE

Monitoring Drive and Database Free Space

SQL Server will autogrow your databases as they run out of space. But the process doesn't manage spa...

ARTICLE

Time and Space: How to Monitor Drive Space in SQL Server

How to monitor drive space in T-SQL and calculate when your drives will run out of space.

ARTICLE

Drive Space Monitoring Gets An Update

Monitoring drive space is something every DBA ought to be doing. Shaun Stuarts brings us a method th...

FORUM

Monitoring NEtwork shares drive space using SQL

Is it possible to monitor network disk space ?

FORUM

Whoops there goes more drive space

Drive space keeps disappearing

 
Contribute