Another alert question

Question

Post reply

Another alert question

Gabriela Nanau-194359

Newbie

Points: 5
More actions
May 20, 2005 at 9:47 am

#93055

How ca I set up a SQL Server alert to be triggered when the processor usage is 90% percent for more than 30 secs?
Any help would be appreciated
Gabriela

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Yelena Varshal SSC-Dedicated Points: 34287 More actions · Answer 1

Gabriela,

I would set up an administrative alert in Performance tools

Start -> Programs -> Administrative Tools ->Performance

Expand Performance Logs and Alerts (on the left) -> Alerts

Right-Click ->New. Give it a name. Peoperties open.

Click Add button under Counters. Mine is opened right on the Processor - %Processor Time (for Total) Click Add. Back to General tab of properties. Enter your percent into Alert when the value is Over.... Fill out other tabs

You may try to use Run This Program (for the Action) and specify VB script that sends an email to your pager or osql in the batch.

Yelena

Regards,Yelena Varsha

Gabriela Nanau-194359 Newbie Points: 5 More actions · Answer 2

Thanks, Yelena, this solves a part of the problem. The thing is that I don't want the alert triggered for spikes, but if the condition is steady. I would like it triggered only if the CPU stays high for a certain amount of time, i.e. 30 secs in my example.

And another thing, can this be done with a SQL Server Agent alert? It seems I cannot find an appropriate performance counter in SQL Server for this!

Thanks anyway.

Gabriela

Yelena Varshal SSC-Dedicated Points: 34287 More actions · Answer 3

Gabriela,

Do you write code? Like VB or VBscript?

I think your questions are very good and as a proof here is a reference to Microsoft article that shows how to create a more custom Performance Alert application. As an example they do use Processor Time, as you need.

http://msdn.microsoft.com/library/default.asp?url=/archive/en-us/dnarperfmo/html/smartalerts.asp

"Alerts Are Cheap Insurance"

They say that code listing for this article is in Knowledge Base article (search on ID number Q215495), "SAMPLE: SmartAlerter Extends PerfMons Alert Mechanism," contains the complete code listing for the SmartAlerter system.

I myself probably would use VBscript using WMI to query performance. I would use several lines of code generated by Scriptomatic tool. see my post about it at

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=5&messageid=183767#bm183832

Then when I get a number I will store it in a sort of a table in SQL Server or maybe just MS Access or even a text file. When a new metric comes I would compare it with the previous one and then script would send an email according to your rules.

Maybe I would implement something similar myself unless someone has a better idea

Yelena

Regards,Yelena Varsha

Gabriela Nanau-194359 Newbie Points: 5 More actions · Answer 4

I read about the possibility to trap events that are registered in the Windows event log with SQL Server alert. For example, if some message is recorded in the application log, like "The execution of the following DTS package failed" this can be trapped by an alert in SQL Server, which will notify some operator. But I never saw an example of how one can actually do this. I think that my problem is simply reduced to this. If there is solution for this I can then trap all sort of conditions. But I am not sure it is possible.

Yelena, Thanks for your time, I will check that article.

Gabriela