Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
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: 4650 | Views in the last 30 days: 9
 
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

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

SCRIPT

Drive space checking monitoring procedure

This procedure can used to monitor the disk drive space details and we can set threshold and it can ...

 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones