THE SQL Server Blog Spot on the Web

Welcome to - The SQL Server blog spot on the web Sign in | |
in Search

Tibor Karaszi

Agent Alerts Management Pack updated

I have updated the Agent Alerts Management Pack, cleaned up the T-SQL code which creates the alerts. You can now just copy, paste and execute that code. Earlier, for some of the some of the alert definitions it just generated the calls to the query result window, and you had to copy this to a query window and execute it. Now you just copy the code and execute it. You find it here:
Published Thursday, September 15, 2011 10:13 PM by TiborKaraszi
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS



Dirk Hondong said:

Hello Tibor,

first of all: thank you for sharing the script. It helped me a lot to set up SQL Agent Alerting in the past.

I just noticed that you´ve updated the script.

There´s one little typo in line 98 (double P at message_id int PRIMARY KEY ...)



September 26, 2011 8:50 AM

TiborKaraszi said:

Thanks Dirk! The tool I use make it HTML (with colors and stuff) sometimes add this extra P, for longer scripts. I've fixed it, thanks for catching it! :-)

September 26, 2011 9:10 AM

Rob Pellicaan said:

Hi Tibor,

2 parts of your article are a bit confusing

1) You say "...We used to be able to enter such "Non Alertable Errors" in the registry key NonAlertableErrors.... This functionality seems to have been removed, I'm afraid."

and later

"I wish we could block out some, but NonAlertableError doesn't seem to work anymore..."

I believe your blogged about the new solution to this issue:

2) In your article it is not clearly stated that, although your specify alerts on severity 16, 17 and 18, not all messages of that severity are logged and therefore alerted. Is it an explicit choice on your behalf not to enable alerting on ALL messages of severity 17 and 18? (as MS recommends in

October 25, 2011 9:00 AM

TiborKaraszi said:


Consiguring whether an error is written to eventlog is one thing - this is what you can control using sp_altermessage. NonAlertableErrors is a different thing - it allow you to block *agent* from triggering on an alert already written to eventlog. Very handy if you capture all alerts with a specific severity level, but want Agent to *not* trigger from some selected in that severity level (but still have them in the EventLog).

2) You mean reconfigure all alerts with 17 and 18 to go to eventlog? I never thought of it from that perspective. Instead I went through sys.messages, error by error and gave each error message some thoughts (notonly 17 and 18)...

October 25, 2011 2:19 PM

Rob Pellicaan said:

Hi Tibor,

I see what you mean now; They are indeed two different things. It is indeed a pity that there is no distinction to be made between raising alerts and writing to the event log. I can even imagine situations where you want certain alerts to be raised without having to write them to the eventlog.

Thank you for sharing the script!

October 26, 2011 7:12 AM

Stefan K. said:

Hi Tibor,

please add Error 833.

SQL Server has encountered %d occurrence(s) of I/O requests taking longer than %d seconds to complete on file [%ls] in database [%ls] (%d).  The OS file handle is 0x%p.  The offset of the latest long I/O is: %#016I64x

July 2, 2012 3:39 AM

TiborKaraszi said:

Done, 833 added. Thanks Stefan!

July 3, 2012 7:40 AM

Leave a Comment


This Blog


Privacy Statement