THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

Where's that sys.messages management pack?

I've often been thinking that "somebody" should produce a list and perhaps some ready-made TSQL code of the SQL Server error messages we want to be alerted for. Something like a Management Pack, but not tied to any particular sys mgmt software. Essentially, what error messages would we typically want to be alerted for, and some ready made TSQL code which defines an alert (msdb.dbo.sp_add_alert, see also http://www.sqlskills.com/BLOGS/PAUL/post/Easy-monitoring-of-high-severity-errors-create-Agent-alerts.aspx) and operator for each of those.

Honestly, how many of you have been going through all SQL Server messages and determined which of those we want to be alerted for? Sure, we can decide to specify alerts for all high-severity level messages (like 19, 20, 21, 22, 23, 24 and 25). But unfortunately the severity classification isn't as coherent and we would hope.

For instance, Paul Randal just recently blogged about error 825, which has as low severity as 10! Do we want to be alerted for 825? You bet! Check out Paul's blog at http://www.sqlskills.com/BLOGS/PAUL/post/A-little-known-sign-of-impending-doom-error-825.aspx.

We can, however make it a little bit easier for us. Since Agent detects the message by sniffing the eventlog, we can immediately discard all messages which aren't written to the eventlog (there's no use defining alert for something which the alerting mechanism never detects, right?):

SELECT 
FROM sys.messages
WHERE language_id 1033
AND is_event_logged 1
ORDER BY severity

Now we are down to 699 messages. It used to be that we could configure whether a message should be written to eventlog (sp_altermessage), but this functionality disappeared in 2005. I haven't checked Connect, but my guess is that if there is such a wish/entry in the first place, it doesn't have too many votes.

My suggestion is that we create some small community project somewhere where we add what messages we want to be alerted for. This can be as simple as a table with error numbers and some insert, and then we use this to generate our sp_add_alert commands based on that. Question is where to do this, so it can be maintained and refined over time? Or if it has already been done by somebody...

 

Published Monday, February 23, 2009 9:37 PM by TiborKaraszi

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

Comments

 

Chris Wood said:

Tibor,

Just what I was starting to worry about. I saw Paul's talk at PASS2008 and added alerts for 823/824/825 and levels 20 thru 25. Then I saw http://www.sqlservercentral.com/articles/65804/ this article and it shows a number of other messages needing possible DBCC CHECKDB action. I would love to know what errors to look for to be safe.

Chris

February 23, 2009 3:45 PM
 

Mike Walsh said:

It almost sounds like you are volunteering and sqlblog.com becomes a great place for such a list ;-)

Seriously though - great point. I would think someplace that has a wiki interface would make sense. I would be willing to see about setting up such a list on SQLServerPedia.com if Brent was fine with it. Could also be on codeplex or the site that the MSDN forum gurus have their "wiki".

I'll wait a week and look at responses. If I don't see any, maybe I'll start something on codeplex or sqlserverpedia

February 23, 2009 9:53 PM
 

SQLBatman said:

I was thinking the same thing about the Wiki, and have an Editor's call tomorrow. I can mention this at that time. I think such a page would fall under the Monitoring section, and we could get the community to update the page going forward.

February 24, 2009 9:13 AM
 

TiborKaraszi said:

Hi all! :-)

No, I don't want to have it here (nor on karaszi.com) since I believe that this should be refined over time (as for which messages to be alerted to). The idea is out there, so I really have no control over where such a project now is created (which is what  wanted ;-) ). My preferences, however:

I definitely and strongly feel it should be somewhere which doesn't require an account. The one possible exception would be codeplex (which I have no expereince of, yet). At least not to use the code/utility. Adding changes probably should require some login.

Using some technology which facilitates modifications over time. Perhaps Wiki is the way to go here - I have no experience of wiki (I'm over 40 years old ;-) ).

KISS

Probably the most simple solution we can do is to have a table with messages and then drive sp_add_alert from that using something like:

CREATE TABLE AlertMessages(errno int)

INSERT INTO AlertMessages values(605)

INSERT INTO AlertMessages values(825)

SELECT

'EXEC msdb.dbo.sp_add_alert

   @name = N'' Alert for error  ' + CAST(errno AS varchar(10)) + ''',' + '

   @message_id = ' +  CAST(errno AS varchar(10)) + ',

   @severity = 0,

   @enabled = 1,

   @delay_between_responses = 0,

   @include_event_description_in = 1;'

FROM AlertMessages

February 24, 2009 11:08 AM
 

kozloski said:

Hi Tibor,

It appears that the ability to run sp_altermessage on system messages has been included in SQL Server 2005 SP3, according to the article containing the list of fixes:

http://support.microsoft.com/?id=955706

A wiki seems like a good idea for sharing which messages to alert on and why.

February 24, 2009 2:36 PM
 

TiborKaraszi said:

Hi kozloski,

Interesting. And indeed, I tried to alter message 1205 to log to eventlog on a 2005 sp3 instance and it worked just fine. This was also reflected in sys.messages. Cool, I had no idea that this was implemented.

This is still not not possible on 2008 (CU1), though, but perhaps it is planned for sp1?

February 25, 2009 3:50 AM
 

Chris Wood said:

Tibor,

I'm thinking of using your simple select * from sys.messages but I added extra checks to only show me the ones that have DBCC in the text. That gave me 33 but I probably will drop the 3 for TRACEON/OFF and the 2 other general DBCC messages leaving me with 28.

As Microsoft produced the Perfomance Dashboard that they could do something for the crucual error messages.

Chris

February 26, 2009 4:51 PM
 

Adam Machanic said:

That's great news about sp_altermessage!  Is that also now (or going to be) available in SQL Server 2008?

February 27, 2009 3:33 PM
 

Mike said:

anybody know what %hs means in message 3014.  

April 13, 2009 11:06 AM
 

Tibor Karaszi said:

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again.

May 14, 2009 4:48 PM
 

TiborKaraszi said:

Just as an FYI:

Since no community project for this took off, I created a small TSQL utility for this, which you can find at my web-site. Hopefully this will be enhanced over time to have the most relevant ones, but no more... :-)

http://www.karaszi.com/SQLServer/util_agent_alerts.asp

June 1, 2009 1:28 PM
 

Tibor Karaszi said:

I have finished my article about suggested SQL Server Agent Alerts. Perhaps calling this a "Management

June 1, 2009 3:16 PM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement