Create email alerts for disk space notification

Last Post 31 Jan 2008 08:00 AM by sqlserverdeveloper. 3 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sqlserverdeveloper
New Member
New Member

--
29 Jan 2008 11:21 AM
This morning I had a problem where db backup job's were failing due to insufficient drive space, So I would like to create email notifications using SMTP if the drive space is reaching certain limit, please let me know how I can do this. Thanks a bunch!


sqlserverdeveloper
New Member
New Member

--
30 Jan 2008 01:15 PM
quote:

Originally posted by: russellb
there are plenty of 3rd party tools that do this, but if you want to create your own, a scheduled job executing this code would do the trick. make step type ActiveX Script. Change # bytes to whatever threshold u want. And of course the IP address of mail server and change email addresses.

Sub Main()
Dim fso 'As Scripting.FileSystemObject
Dim dr 'As Scripting.Drive
Dim AvailableBytes 'As Long
Dim TotalBytes 'As Long

Set fso = CreateObject("Scripting.FileSystemObject")
Set dr = fso.GetDrive("C:")

AvailableBytes = dr.AvailableSpace
TotalBytes = dr.TotalSize
' not using total, just put it there in case u want to do percentages

If AvailableBytes < 1073741824 Then ' 1GB
sendEmailAlert
End If

Set dr = Nothing
Set fso = Nothing
End Sub

Sub sendEmailAlert()
Dim m
Set m = CreateObject("CDO.Message")

With m
.Configuration.Fields("http://schemas.microsoft.com/cdo/co.../sendusing").Value = "2"
.Configuration.Fields("http://schemas.microsoft.com/cdo/co...smtpserver").Value = "SMTP SERVER IP ADDRESS HERE"
.Configuration.Fields.Update

.To = "you@yourdomain.com"
.From = "you@yourdomain.com"
.Subject = "Drive Space Alert"
.TextBody = "Drive C: has less than 1GB Free!"
.Send
End With

Set m = Nothing
End Sub

Edit: fixed tiny bug

-- Russell --
------------------------------------------
SQL Server Magazine Forum Pro


When I used this code, by changing the IP address and email address
getting the below error:
Error 14277: The command script does not destroy all the objects that it creates. Revise the command script. The job was not saved. PLease let me know. Thx.


sqlserverdeveloper
New Member
New Member

--
31 Jan 2008 05:25 AM
quote:

Originally posted by: russellb
strange. you are right, i am getting that error in sql2k5 too. has to be a bug in sql2k5 script parsing engine i think. all of the objects are definitely destroyed.

i guess you could save it as a vbs file and have a job execute the file.

-- Russell --
------------------------------------------
SQL Server Magazine Forum Pro


Just to confirm I am using sql 2000. Not sure, what you meant by saving as vbs file, please advice. Thanks!
sqlserverdeveloper
New Member
New Member

--
31 Jan 2008 08:00 AM
Thank you very much! You rock!!
Sorry if I am asking a lot, but the script which is provided only looks at a specific drive, what if we want a email notification if any one of the drives is running out of space without hardcoding. I greatly appreciate your help.
You are not authorized to post a reply.

Acceptable Use Policy