Monitoring Free Space on Microsoft SQL Server

Introduction

Database administrators need to take up the stewardship of the server; any issues and be prepared to burn the midnight oil. A job goes down or a database fails to respond or the server fails to start, DBAs are always on the hot seat. Today, we will be discussing a very important aspect of disk usage and space monitoring. It’s one of the fundamental duties of a DBA to manage and monitor the disk usage.

All the databases reside physically in .MDF .LDF files on the drives. There are jobs running, backups taken, maintenance plans and most importantly the database keeps growing. In this scenario, monitoring the Server to ensure that there is enough space to support the growing demands is really important.

There are a lot of different ways in which we can monitor the drives for free spaces. I would be discussing a very simple yet effective way that does the trick for us, “xp_fixeddrives”. SQL Server offers this undocumented, extended stored procedure for monitoring free spaces on the system that hosts the SQL Server.

Executing the below statement gives the system details. All the available drives and the free spaces in MB on each of them are listed:

EXEC master.dbo.xp_fixeddrives
drive MB free
----- -----------
C     38126
D     79906

(2 row(s) affected)

It is not feasible for a DBA to run this statement every 30 mins and check if the system has enough free space. Trust me, DBAs are no free birds? So let us try to make our job a little less tiresome.

CREATE PROCEDURE [dbo].[MonitorFreeSpace]

AS

SET NOCOUNT ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON

BEGIN

DECLARE @ThreshHold INT
DECLARE @COUNT INT
DECLARE @CatchDrive CHAR(1)
DECLARE @SubStr VARCHAR(2000)
DECLARE @MsgStr VARCHAR(2000)

/*We have set the thresh hold limit for our drives as 2 GB*/
SET @ThreshHold=2048

/*Table variable to host drives & free spaces details*/
DECLARE  @SpaceInfo TABLE(
ID INT IDENTITY(1,1),
Drive CHAR(1),
MBFreeSpace INT)

INSERT INTO @SpaceInfo EXEC xp_fixeddrives

SELECT @COUNT=MIN(ID) FROM @SpaceInfo
/*Looping through all the drives */
  WHILE (@COUNT IS NOT NULL AND @COUNT<=(SELECT MAX(ID) FROM @SpaceInfo))
	BEGIN
      /*Checking for the free space on the drive against preset threshhold */
	IF ((SELECT MBFreeSpace FROM @SpaceInfo WHERE ID=@COUNT)<@ThreshHold)
		BEGIN

		 SELECT @CatchDrive=Drive FROM @SpaceInfo WHERE ID=@COUNT
		 SET @SubStr='SERVER MyServerName - Available Space on _
		 '+@CatchDrive+ ': Drive below critical limit.'
		 SET @MsgStr='Attention !! The free space on _
		 '+@CatchDrive+': drive is below the prescribed _
		 threshold of'+CONVERT(VARCHAR(20),@ThreshHold)+'MB.'

		 /*Send mail to the concerned  for ASAP assistance*/
		 EXEC master.dbo.xp_sendmail
		 @recipients =N'abc.xyz@microsoft.com',
		 @subject =@SubStr,
		 @message =@MsgStr

		END
     SET @SubStr=''
     SET @MsgStr=''
     SET @COUNT+=1
     /*check for the next drive*/
	END
END

Here, we have created a very simple procedure. Let me tell you what it does. Initially, we’ve set a threshold limit of 2 GB which could vary on different servers depending upon server usage and size of the drives on the host system. Next, we get the list of all the drives available on the server and also the free spaces in each of them. We loop through all these drives to check if the available space is less than the preset threshold limit. If it is so, we will send a mail to the DBA reporting the same for immediate assistance. It was really simple isn’t it? But we do need to give a thought to automation of this procedure so that we can get a nap at times.

We could automate this procedure to run in a desirable frequency depending upon our requirement and the criticality of the Server. Using SQL Agent, we can create a Job with T-SQL step for executing this procedure and have a schedule in place for the job. Alternatively, we can trigger the procedure from a SQLCMD or OSQL via a .CMD file and schedule the CMD in Windows scheduler. The third option is, in case the DBA needs this information to be scanned each time the server is booted, we can configure this SP to be executed each time the server re-boots via below segment of code.

EXEC sp_configure 'scan for startup procs',1;
RECONFIGURE ;

EXEC sp_procoption @ProcName =  'MonitorFreeSpace'
    , @OptionName =  'startup'
    , @OptionValue =  '1';

The idea is to get the intimation much before the damage is done as per the need and criticality.

We can also log the disk usage information into a table by comparing free space between intervals. This should be done regularly to help derive a trend and plan accordingly.

Administering the disk usage is really important as this will help the DBA to get things in place in time, else one would live in fear of running out of space and also putting all the database supported applications in a critical state.

History

• 28^th February, 2011: Initial post