THE SQL Server Blog Spot on the Web

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

Tibor Karaszi

sp_altermessage is back in business!

Just a quick note that we again can modify whether system messages are to go to eventlog/errorlog again. I.e., we can change the is_event_logged column in sys.messages. This is very valuable in general and specifically is you want to define Agent alerts (for which Agent polls the Eventlog). For instance:

SELECT * FROM sys.messages
WHERE message_id = 1205
AND language_id = 1033

Notice the value for the is_event_logged column. Now, run below:

EXEC sp_altermessage
 @message_id = 1205
,@parameter = 'WITH_LOG'
,@parameter_value = 'true'

Now, re-run the select statement and see that you modified the behavior for the system message. Now, re-run the sp_altermessage with 'false' to reset to default.

The ability to modify this behavior for system messages was available prior to SQL Server 2005, but some re-architecturing in 2005 removed the functionality. kozloski informed me in this blog post that 2005 sp3 re-introduced the functionality and obviously as of sp1 the functionlity is back in 2008 as well.


Published Thursday, May 14, 2009 11:36 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



GrumpyOldDBA said:

That's really good to know - I did raise this but was told that the functionality was not going to be replaced. Over the years this has been a feature I have used extensively, especially in test/stress systems.

May 19, 2009 4:12 AM

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

Matt said:

Thanks for this, very useful to know!

November 20, 2013 1:31 AM

Leave a Comment


This Blog


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