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

zhang said:    chrome store    Nike Jordans    michael kors australia    yeezy boost 350 shoes    coach factory outlet    louis vuitton us    michael kors australia    snow boots uk    pandora ring    michael kors    kate spade handbags    Air Max 90 Femme    pandora uk    business blog    ray ban sunglasses    official coach outlet site    kate spade canada    myhermes parcel shop    pandora charms    Nike Schuhe Damen    Wave Prophecy 2 Shoes    puma shoes    louboutin shoes    sunglasses hut    Nike Air Max 2017    gel kayano    fun games for kids    chanel outlet    birkenstock outlet    cheap ray ban    michael kors outlet    Obat Kuat Sex    puma oulet sale    kate spade    coach factory outlet online    clb shoes sale    north face outlet    Jared Jewelry - Official    Nike Free    kate spade outlet    ray ban frames    michael kors    kate spade outlet    coach factory outlet    kate spade outlet    China wholesale    ray ban sunglasses    oakley outlet    coach outlet online    kate spade new york store    ray ban uk    chanel outlet    jimmy choo outlet    birkenstock canada    payless shoes    pandora australia    black friday deals online    ray ban sunglasses    Prada Shoes - Official    fulam shoes    articles    pandora rings uk    burberry outlet    pandora uk    ugg boots    tory burch outlet    jimmy choo heels    louis vuitton outlet    nbastore    timberland boots    school bags online    nike huarache shoes    Louis Vuitton Outlet    omega watches    clearance    Prada Sunglasses - Official    michael kors outlet    nike outlet    Kay Jewelers - Official    ray ban justin    Air Jordan Homme    Dreamland Jewelry - Official    burberry outlet sale    pandora necklace    Nike Air Max 90 Mes    Nike Blazer Femmes    nike outlet    puma shoes    Online Oyun    timberland boots    michael kors uk    michael kors uk    Nike Boots    Engagement Rings - Official    Wholesale womens autumn winter clothing    under armour outlet    mulberry outlet    coach handbags sale    black friday deals    yeezy boost 350    louis vuitton    Kaufen Nike Air Max 1    Air Max 1 Homme    michael kors australia    pandora jewelry sale    michael kors australia official    Louis Vuitton Online    michael kors australia    ray-ban glasses    soccer store    pandora outlet    hermes outlet    The Retail Compliance Association    Nike Free Run    Nike Schuhe Herren    tory burch outlet    Louis Vuitton Outlet    ray ban wayfarer sunglasses    michael kors    official louis vuitton    ray ban shop    Nike Air Max 180    Prada Shoes - Official    mulberry outlet    burberry outlet coupon


January 9, 2018 9:23 PM

Leave a Comment


This Blog


Privacy Statement