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)

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.