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
WHERE SpaceFreeMB < @Threshold
DROP TABLE #DiskSpace
SET NOCOUNT OFF
Please log in again. The login page will open in a new window. After logging in you can close it and return to this page.