Great script. I used to use srvinfo.exe but this is a much more elegant solution. I made some minor adjustments to email me and additional checking when disk space reaches a critical point.

I found the bottom bit in a forum somewhere. I think it's this one. Sorry I lost the author's name.

Anyway, here you go:-

/*

we use this table to store all DBA task

*/

use ServerAdmin

go

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ssp_DiskSpace]')

and OBJECTPROPERTY(id, N'IsProcedure') = 1)

drop procedure [dbo].[ssp_DiskSpace]

GO

CREATE PROCEDURE ssp_DiskSpace

@ServerName sysname

AS

SET NOCOUNT ON

DECLARE @emailaddress VARCHAR (100)

DECLARE @SubjectText VARCHAR (200)

DECLARE @SubjectText1 VARCHAR (200)

DECLARE @MSG VARCHAR(400)

DECLARE@DSPACE INT

DECLARE@threshold INT

DECLARE @hr INT

DECLARE @fso INT

DECLARE @drive CHAR(1)

DECLARE @odrive INT

DECLARE @TotalSize VARCHAR(20)

DECLARE @MB BIGINT ; SET @MB = 1048576

IF @ServerName is null or @ServerName =''

BEGIN

SELECT @ServerName = @@servername + '.master.dbo.xp_fixeddrives'

END

ELSE

SELECT @ServerName = @ServerName + '.master.dbo.xp_fixeddrives'

CREATE TABLE TBLdrive (

ServerName varchar(15),

drive char(1) PRIMARY KEY,

FreeSpace int NULL,

TotalSize int NULL,

FreespaceTimestamp DATETIME NULL

)

INSERT TBLdrive(drive,FreeSpace)

EXEC @ServerName

-- This is a VB method

EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

DECLARE dcur CURSOR LOCAL FAST_FORWARD

FOR SELECT drive from TBLdrive

ORDER by drive

OPEN dcur

FETCH NEXT FROM dcur INTO @drive

WHILE @@FETCH_STATUS=0

BEGIN

EXEC @hr = sp_OAMethod @fso,'GetDrive', @odrive OUT, @drive

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

EXEC @hr = sp_OAGetProperty @odrive,'TotalSize', @TotalSize OUT

IF @hr 0 EXEC sp_OAGetErrorInfo @odrive

UPDATE TBLdrive

SET TotalSize=@TotalSize/@MB, ServerName = replace( @ServerName , '.master.dbo.xp_fixeddrives',''), FreespaceTimestamp = (GETDATE())

WHERE drive=@drive

FETCH NEXT FROM dcur INTO @drive

END

CLOSE dcur

DEALLOCATE dcur

EXEC @hr=sp_OADestroy @fso

IF @hr 0 EXEC sp_OAGetErrorInfo @fso

-- Email DBA with disk usage information

SELECT @EmailAddress=Email_Address FROM msdb..sysoperators WHERE Name='YOUR NAME'

SET @SubjectText = 'Disk space usage on Server ' + @@ServerName

EXEC master..xp_sendmail

@recipients = @emailaddress,

@subject = @SubjectText,

@query ='SELECT

ServerName,

drive,

TotalSize as [Total(MB)],

FreeSpace as [Free(MB)],

(TotalSize - FreeSpace) as [Used(MB)],

CAST((FreeSpace/(TotalSize*1.0))*100.0 as int) as [Free(%)],

(CONVERT(char(12), FreespaceTimestamp, 105)) AS [Date]

FROM ServerAdmin..TBLdrive ORDER BY drive',

@width = 133

-- where 105 is dd-mm-yyyy date format

-- Email DBA if disk space falls below the specified threshold

--SET @threshold = 20480-- For testing only. 20GB set as limit

SET @threshold = 1024-- Set the limit of disk usage threshold for all drives

SET @drive = (SELECT TOP 1 drive FROM ServerAdmin..TBLdrive --get first drive letter

WHERE freespace < @threshold

ORDER BY drive ASC)

SET @DSPACE = (SELECT freespace FROM ServerAdmin..TBLdrive --get the disk space for the letter

WHERE drive = @drive)

SET @MSG = @drive + ' is at ' + CONVERT(VARCHAR,@DSPACE) --put the vars into a msg

+ 'MB' + CHAR(13) + CHAR(10)

WHILE (SELECT COUNT(*) FROM ServerAdmin..TBLdrive WHERE freespace @drive) > 0

BEGIN--loop through drive letters and repeat above

SET @drive = (SELECT TOP 1 drive FROM ServerAdmin..TBLdrive

WHERE freespace @drive

ORDER BY drive ASC)

SET @DSPACE = (SELECT freespace FROM ServerAdmin..TBLdrive

WHERE drive = @drive)

SET @MSG = @MSG + @drive + ' is at ' + CONVERT(VARCHAR,@DSPACE) + 'MB'

+ CHAR(13) + CHAR(10) + CHAR (10) +'Please investigate immediately.'

SET @SubjectText1 = 'WARNING: Disk space in one or more drives is below 1 GB'

EXEC master..xp_sendmail

@recipients = @emailaddress,

@subject = @SubjectText1,

@message = @msg,

@width = 133

END

DROP TABLE ServerAdmin..TBLdrive

RETURN

GO