Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

SMTP SQL Server Performance Alerts

By Mike Metcalf,

Most DBA’s are already more than familiar with the idea of using SQL Mail (or some SMTP alternative) to notify them when something goes wrong inside SQL Server, however, some of them may be missing a trick in forgetting to monitor the server resources that SQL is using.

This article concentrates on a method that allows the sending of alert emails via an SMTP server from outside of SQL (based on the alerts in Performance Monitor). Why do we need to do this you may ask, well, wouldn’t it be nice to get an email telling you the CPU was maxed out or that the available space on the server had dropped below 20% rather than having to go and look for yourself?

If nothing else it allows you to be more proactive than before, rather than wait for someone to complain, you get the alert email and are already part of the way through fixing it when the phone rings, it’s always good to be able to say to someone ringing up to complain about system performance that you already know about the problem and have been working on fixing it for a couple of minutes now, or better yet that you’ve already resolved it (this sort of thing is good to bring up around salary review time).

Anyway, enough of the justification, on with the show…..

Here’s the script you need for the main file (change the bits in blue to what you want, don’t forget to put in the name (or IP Address) of your SMTP Server in the lower half)

' Get command line parameters

Dim ArgObj

Set ArgObj = WScript.Arguments

Dim strFrom, strTo, strSubject, strBody

strFrom = "SQL-Cluster <SQL-Cluster@here.com>"

strTo = "Mike <mike.metcalf@4projects.com>"

strSubject = "Automated CPU Alert from SQL-Cluster"

' get the body from the command line

If ArgObj.Count > 0 Then

strBody = ArgObj( 0 )

' if the subject is specified as an argument then add it

If ArgObj.Count > 1 Then

strSubject = ArgObj( 1 )

End If

Else

strBody = "Default alert message body"

End if

Call SendEmail( strFrom, strTo, strSubject, strBody )

' release memory

Set ArgObj = Nothing

' Sub-routing to send an e-mail using the CDO component

Sub SendEmail(sFromEmail, sToEmail, sSubject, sText )

Dim objMail

Set objMail = CreateObject( "CDO.Message" )

objMail.From = sFromEmail

objMail.To = sToEmail

objMail.Subject = sSubject

' Send using an SMTP server

objMail.Configuration.Fields.Item( "http://schemas.microsoft.com/cdo/configuration/sendusing" ) = 2

' Name or IP of remote SMTP server

objMail.Configuration.Fields.Item( "http://schemas.microsoft.com/cdo/configuration/smtpserver" ) = "SMTPServer@here.com"

' Server port

objMail.Configuration.Fields.Item( "http://schemas.microsoft.com/cdo/configuration/smtpserverport" ) = 25

objMail.Configuration.Fields.Update

objMail.TextBody = sText

objMail.Send

Set objMail = nothing

End Sub

What to do with the Script

Create a new folder in Program files on your server called VBS SCRIPTS and save the script into a file called SEND-MAIL.VBS (if you create it first don’t forget to click edit rather than open as open will try and execute the contents of the file).

Testing that the Script works

This couldn’t be easier, launch a command prompt and navigate to your Windows\system32 folder (or WINNT\SYSTEM32 in Windows 2000) and enter the following:

CSCRIPT "C:\Program Files\VBS Scripts\send-mail.vbs" "This is a test message to see whether the send-mail script works" "Testing"

Assuming that you changed the details in the file properly (and your SMTP server is configured correctly) you should have an email sitting in your inbox sometime in the next couple of minutes..

Great, now that you have the .vbs script file created and have tested that it works you can set about deciding what you want to create an alert for.

Creating the Alert

This too is an incredibly simple process

As you can see below, I have chosen to monitor several different counters on one of our servers but the choice is entirely up to you, if it’s got a performance counter then you can create an alert for it.


  • Launch Performance Monitor on the Server you want to create the alert for
  • Expand the tree on the left hand side till you get to the Alerts branch
  • Right-Click on Alerts
  • Select New Alert Settings
  • Enter a Name for the alert (this is fixed so make sure it’s got the right name before proceeding any further), For this article I’ve called mine Monitor 1 but obviously this can be whatever you want it to be.

  • Click on OK to continue
  • Enter a description for the alert in the Comment box (below); if possible try to make it something easily recognisable without having to open the alert up again at a later date.

Once that’s done click on the ADD button and select the performance counter you want to create the alert for, there are already a great many articles available that tell you what to monitor so I’ll let you decide that for yourself. If you’re monitoring free space on disks you can either add all of them into the same alert (setting alert criteria for each in turn) or create a separate alert for each of them.

As you can hopefully see, I’ve created an alert condition for the %Processor Time (_Total) counter, basically what this screen is saying is that a condition is checked every 30 seconds and an alert is generated when the %Processor Time is above 90%.

Next, click on the Action Tab and tell the alert to run the cscript.exe application which can be found at the following location

C:\WINDOWS\SYSTEM32\CSCRIPT.EXE

(This is the path for Windows 2003, if you have Windows 2000 you’ll need to change WINDOWS to WINNT to make it work)

Next Click on the Command Line Arguments and make sure only the Text Message box is ticked (as per below), don’t worry about what to put in the text box as that’s the next step.

In the Text Message box enter the following

C:\Program Files\VBS Scripts\send-mail.vbs" "The CPU on the Server has exceeded 90%" "Server CPU Alert

(You need to use the double quotes around each entry but not at the start or finish as they are filled in automatically for you when you click on OK (I have no idea why, probably a ‘feature’).

The middle portion is the main body of the email and the last part is the subject line; you can change these to whatever you require for your alert and then click on OK to return to the previous screen. Click on the Schedule Tab and set the Alert to start manually; or if you prefer let it start automatically (I tend not to do it that way as I want to be sure it actually started)

Click on the OK button to create the alert and return back to the main Performance Monitor window. Finally, right-click on the new alert and select Start to begin the monitoring process (assuming you’re starting it manually).

And that’s it, quite a long article because of the pictures but I’m sure you’ll agree it’s not rocket science.

Total article views: 12916 | Views in the last 30 days: 7
 
Related Articles
FORUM

Issues creating alerts in Performance Monitor

Windows Server 2008 Performance Monitor will not execute powershell script

FORUM

Sending email alert when SQL Server restarts

Email alerts in SQL Server 2005

FORUM

Alert to monitor DBCC CheckDB

Configure an Alert to monitor when DBCC CheckDb finds an error

FORUM

Alert/Monitor Notfication for SSRS/SSAS

Alerting and Monitoring for SSAS/SSAS

ARTICLE

dbWarden - A Free SQL Server Monitoring Package

dbWarden is a comprehensive monitoring and alerting solution for SQL Server 2005 or newer. It featur...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones