Automatic Email Alerts for Low SQL Server Disk Space

Tuesday Sep 20th 2005 by Muthusamy Anantha Kumar aka The MAK
Share:

While Auto Growth is a good feature, it can eat up hard disk space. This article illustrates how to set up email notification when SQL Server is running out of Hard Disk Space.

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

Parameters Explained

@out is the output variable for the procedure usp_diskspace

@From is the "From" address.

@To is the "To" address.

@Subject is the Subject of the email.

@minimumspace is the minimum amount of hard disk space needed in %

@SMTPServer is the SMTP Server name on your network.

Note: Please update all the above parameters according to your environment.

Once the above code is executed in Query Analyzer, the usp_diskspace collects information from your hard disk drive and sends an email using usp_send_cdosysemail if any of the drives are running out of space when compared the minimum space requirement. Refer Fig 1.0.

Click for larger image

Fig 1.0

Step 4

Create a Scheduled job, to be executed every hour, to execute the code from step 3. Refer Fig 1.1, Fig 1.2 and Fig 1.3.

Note: Please update all the above parameters according to your environment.


Fig 1.1


Fig 1.2


Fig 1.3

Conclusion

This article has illustrated how to set up email notification when SQL Server is running out of Hard Disk Space.

» See All Articles by Columnist MAK

Share:
Home
Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved