Click here to Skip to main content
15,867,686 members
Articles / Database Development / SQL Server
Article

Monitoring Free Space on Microsoft SQL Server

Rate me:
Please Sign up or sign in to vote.
4.64/5 (7 votes)
28 Feb 2011CPOL3 min read 30.9K   22   4
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:

SQL
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.

SQL
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.

SQL
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

  • 28th February, 2011: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Database Developer
India India
I am a Microsoft certified Technology Specialist in MS SQL Server 2008 and 2005.I have fair amount of experience and expertise in MS database development, administration and modeling and MS BI. Started my career in 2007 and primarily into MS databases, and later diversified into MS BI especially SSIS. I also have a little exposure in Oracle 10g while working on one of the migration projects. But MS SQL Server is my passion!

Firm believer in knowledge grows from sharing, I really like reading books, trying new features and sharing the little that I know. Unless we're willing to have a go, fail miserably, have another go, success won't happen. The best thing I have discovered about myself is the "never say die" attitude. The best comment I have ever received is when my manager at my first job said "when this guy works he throws away his watch".

Comments and Discussions

 
GeneralError in Line - SET @COUNT+= 1 Pin
anandRIL3-Mar-11 17:54
anandRIL3-Mar-11 17:54 
GeneralRe: Error in Line - SET @COUNT+= 1 Pin
Keshav Singh3-Mar-11 18:21
Keshav Singh3-Mar-11 18:21 
Generaluseful Pin
vegeta4ss28-Feb-11 5:48
vegeta4ss28-Feb-11 5:48 
GeneralMy vote of 5 Pin
saumya verma28-Feb-11 2:31
saumya verma28-Feb-11 2:31 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.