Automatic Email Alerts for Low SQL Server Disk Space

September 20, 2005

Usually SQL Server database administrators use the feature "Auto Growth" in the database configuration, in order allows automatic file growth when the database tries to expand. Though it is a good feature, sometimes SQL Server runs out of hard disk space. This article illustrates how to set up email notification when SQL Server is running out of Hard Disk Space.

Note: The following procedures were created and tested in SQL Server 2000. Your SQL Server box should be able to send SMTP emails.

Step 1

Create the following procedure on the SQL Server for which you would like to monitor the disk space. Please download usp_diskspace.sp.

use master
go
SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO
Create PROCEDURE  usp_diskspace 
@Percentagefree int,
@error2 varchar(8000) OUTPUT
AS
SET NOCOUNT ON
DECLARE @hr int
DECLARE @fso int
DECLARE @drive char(1)
DECLARE @odrive int
DECLARE @TotalSize varchar(20)
DECLARE @MB bigint 
SET @MB = 1048576
DECLARE @COUNT int
DECLARE @Maxcount int
DECLARE @error varchar(700)
DECLARE @errordrive char(1)
DECLARE @errortotalspace varchar(20)
DECLARE @errorfreespace varchar(20)
DECLARE @free int
DECLARE @date varchar(100)
declare @query varchar(1300)
set @date = convert(varchar(100), getdate(),109)
set @error2=''
select @query= 'master.dbo.xp_fixeddrives'
CREATE TABLE #drives (id int identity(1,1),ServerName varchar(15),
drive char(1) PRIMARY KEY,
FreeSpace int NULL,
TotalSize int NULL,
FreespaceTimestamp DATETIME NULL)
INSERT #drives(drive,FreeSpace)
EXEC @query
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 #drives
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 #drives
SET TotalSize=@TotalSize/@MB, 
 ServerName = replace( @query , 
 '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
set @maxcount =(select max(id) from #drives)
set @count=1
while @count <=@maxcount
begin
select @errortotalspace =
 convert(varchar(20),
 Totalsize), 
 @errorfreespace =freespace, 
 @free=CAST((FreeSpace/(TotalSize*1.0))*100.0 as int),
 @errordrive=Drive from #drives where id = @count
if @free<@percentagefree
begin
set @error = 'Server = 
 '+@@servername+': 
 Drive=' + @errordrive+': 
 Percentage free=' +convert(varchar(2),
 @free)+'% TotalSpace ='+ @errortotalspace  +'MB : 
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date 
set @error2=@error2+@error+char(13)
end
else
begin
set @error = 'Server = 
 '+@@servername+': 
 Drive=' + @errordrive+': 
 Percentage free=' +convert(varchar(2),
 @free)+'% TotalSpace ='+ @errortotalspace  +'MB : 
 FreeSpace ='+ @errorfreespace +'MB :Date =' +@date 
end
set @count=@count+1
end
DROP TABLE #drives
set @date = convert(varchar(100), getdate(),109)

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Step 2

Create the following procedure on the SQL Server for which you would like to monitor the disk space. Please download usp_send_cdosysemail.sp.

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

create  PROCEDURE usp_send_cdosysmail
@From varchar(500) ="",
@To varchar(500) ,
@Subject varchar(500)=" ",
@Body varchar(4000) =" ",
@SMTPServer varchar(25)=""
AS
Declare @iMsg int
Declare @hr int
Declare @source varchar(255)
Declare @description varchar(500)
Declare @output varchar(1000)
EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT
EXEC @hr = sp_OASetProperty @iMsg,
'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- Replace MailServerName by the name or IP of your SMTP Server.
EXEC @hr = sp_OASetProperty @iMsg, 
 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', @SMTPServer 
EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null
EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject
-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
EXEC @hr = sp_OASetProperty @iMsg, 'HTMLBody', @Body
EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL
-- Sample error handling.
IF @hr <>0 
select @hr
BEGIN
EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END
END
-- Do some error handling after each step if you need to.
-- Clean up the objects created.
EXEC @hr = sp_OADestroy @iMsg


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

Step 3

By using the code below, we can test the procedures. Download TestProc.sql.

declare @out varchar(8000)
declare @From varchar(200)
declare @To varchar(200)
declare @subject varchar(300)
declare @SMTPServer varchar(100)
declare @minimumspace int
set @minimumspace = 15
set @SMTPServer ='MySMTPServer'
set @From ='mak_999@yahoo.com'
set @To ='mak_999@yahoo.com'
set @subject = 'Running out of Hard Disk space on the Server: '+@@servername
exec usp_diskspace @minimumspace,@out OUTPUT
if @OUT is not null or ltrim(rtrim(@OUT))<>'' 
begin
exec usp_send_cdosysmail @From ,@To ,@Subject,@OUT,@SMTPServer
end