Get Space on a Server Drive

This script is used to return the amount of free disk space (in MB) on a server.

Input parameter @UNCPath is the UNC path for the drive. eg. \\servername\drive$

Returns free disk space in MB.
A value of -1 means no drive exists.

Requirements:

The account running the stored procedure must have administrative access to the drive.
Access to run xp_cmdshell must be granted.

Suitable For:

2005, 2008

Download:

Download GetServerDriveSpace.zip

Example Script:

-- Check drive sizes.
-- This example checks two drives.
-- There is no reason why you could not also check other associated servers so long 
-- as the SQL service account has the appropriate access to the associated servers.

DECLARE @DriveFreeSize int
DECLARE @UNCPath VARCHAR(80)
DECLARE @MailRecipients VARCHAR(100)
DECLARE @MinimumSpaceMB int
DECLARE @MSG VARCHAR(1000)

SET @MailRecipients = 'SQLAdmin@domain.com'

SET @UNCPath = '\\server1\c$'
SET @MinimumSpaceMB = 1024
EXEC @DriveFreeSize = GetServerDriveSpace @UNCPath
IF @DriveFreeSize < @MinimumSpaceMB
BEGIN
    SET @MSG = 'WARNING: ' + @UNCPath + ' LOW ON DISK SPACE. SPACE REMAINING = ' + CONVERT(VARCHAR(10),@DriveFreeSize) + ' MB.'
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = @MailRecipients
        ,@subject = @MSG
        ,@body = @MSG
        ,@importance = 'HIGH'
END

SET @UNCPath = '\\server1\e$'
SET @MinimumSpaceMB = 2048
EXEC @DriveFreeSize = GetServerDriveSpace @UNCPath
IF @DriveFreeSize < @MinimumSpaceMB
BEGIN
    SET @MSG = 'WARNING: ' + @UNCPath + ' LOW ON DISK SPACE. SPACE REMAINING = ' + CONVERT(VARCHAR(10),@DriveFreeSize) + ' MB.'
    EXEC msdb.dbo.sp_send_dbmail 
        @recipients = @MailRecipients
        ,@subject = @MSG
        ,@body = @MSG
        ,@importance = 'HIGH'
END