January 2010 Tip of the Month

SQL Server Administration:  Monitor disk space free with a simple routine.  Make sure that you have room on your drives to hold the work being performed.  If you run out of disk space, your SQL Server will stop accepting requests.  In the code below, the @Threshold variable is designed to hold a value in MB that is the amount of space that should be available on any drive connected to your SQL Server.  Change the @Threshold value to find drives that do not have enough space free.  The routine below will return a list of drives that have less than 500MB free.

SET NOCOUNT ON

DECLARE @Threshold NUMERIC (5, 0)

SET @Threshold = 500  — 500MB

CREATE TABLE #DiskSpace (

Drive VARCHAR (2),

SpaceFreeMB NUMERIC (10, 2))

INSERT INTO #DiskSpace (Drive, SpaceFreeMB)

EXEC ( ‘master..xp_fixeddrives’ )

SELECT Drive, SpaceFreeMB

FROM #DiskSpace

WHERE SpaceFreeMB < @Threshold

DROP TABLE #DiskSpace

SET NOCOUNT OFF