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?

This blog has moved! You can find this content at the following new location:

http://sqlblog.karaszi.com/wheres-that-sys-messages-management-pack/

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

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
 

a.m. 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
New Comments to this post are disabled
Privacy Statement