Find Disk Free Space in Sql Server 2005 or 2008

Execute following procedue in Query window

Use Master
GO
EXEC master..XP_FixedDrives
GO

Example:

Send Disk Space Alerts using SQL Server 2005

Step 1: Create the database mail profile account using SQL Server Management Studio. Give the profile name to “FreeSpaceAlertMails”

Step2: Create the below procedure in master database which will check the disk space.

CREATE PROCEDURE sendAlertMails
AS
BEGIN
SET NOCOUNT ON
DECLARE @availableSpace AS FLOAT
DECLARE @alertMessage AS Varchar(4000)

CREATE TABLE #tbldiskSpace
(
driveName VARCHAR(3),
freeSpace FLOAT
)

INSERT INTO #tbldiskSpace EXEC master..XP_FixedDrives
SELECT @availableSpace = ROUND((freeSpace)/1024,1) FROM #tbldiskSpace WHERE driveName = ‘E’

SET @alertMessage = ‘(host:yourpool.sqldbpool.com)E:\ Disk Space Critical. Free Space Available on E:\ Drive is ‘ + CAST(@availableSpace AS VARCHAR) + ‘GB’

IF @availableSpace < 10
BEGIN
EXEC msdb.dbo.sp_send_dbmail
@profile_name = ‘FreeSpaceAlertMails’,
@recipients = ‘yourmail@gmail.com’,
@body = @alertMessage,
@importance = ‘High’,
@subject = ‘domain.com Disk Critical E Drive’ ;
END

DROP TABLE #tbldiskSpace

END