ERRORLOG messages from sys.messages

Question

Post reply

ERRORLOG messages from sys.messages

Radiance

SSC Eights!

Points: 822
More actions
October 16, 2014 at 8:04 am

#297519

Hi folks,
Direct (and very basic I guess) question: All texts from sys.messages marked with is_event_logged = 1 is actually logged or there any exceptions?
select * from sys.messages where is_event_logged = 1
and language_id = 1033
I'm not sure about this, and I really want to kill this doubt.
Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

george sibbald SSC Guru Points: 104210 More actions · Answer 1

all are logged, see http://msdn.microsoft.com/en-us/library/ms187382(v=sql.110).aspx

---------------------------------------------------------------------

Radiance SSC Eights! Points: 822 More actions · Answer 2

Well...if I update a logged message to 0 (aka please don't log it), then, why the message still be logged?

george sibbald SSC Guru Points: 104210 More actions · Answer 3

how did you update it?

---------------------------------------------------------------------

Radiance SSC Eights! Points: 822 More actions · Answer 4

Using SP_ALTER_MESSAGE.

My test: setting is_logged_message from 1 to 0, but the message still be in ERRORLOG when certain event occurs. It's like SQL Server bypassed the change...

Maybe there any exceptions, and my sample of messages isn't a good candidate to play thist test.

[]'s

CKX SSCrazy Points: 2519 More actions · Answer 5

Radiance (10/23/2014)
Using SP_ALTER_MESSAGE.
My test: setting is_logged_message from 1 to 0, but the message still be in ERRORLOG when certain event occurs. It's like SQL Server bypassed the change...
Maybe there any exceptions, and my sample of messages isn't a good candidate to play thist test.
[]'s

What version of Sql Server are you using?

Maybe, this post can help...

http://sqlblog.com/blogs/tibor_karaszi/archive/2009/05/14/sp-altermessage-is-back-in-business.aspx

Radiance SSC Eights! Points: 822 More actions · Answer 6

Hi CKX,

I'm using SQL Server 2008 R2 EE.

Thanks for the post, I already saw it weeks ago.

My question is: logged a message that isn't not logged by default (for instance, message_id = 113) using sp_altermessage works, but if I change the is_event_logged of a event who is logged by default (for instance, message 17184 = "The error log has been reinitialized. See the previous log for older entries."), SQL Server bypass the change and continues to logging same way.

-- The message: The error log has been reinitialized

SELECT * FROM sys.messages

WHERE 1=1

AND language_id = 1033

and message_id = 17184

AND is_event_logged = 1

-- Change the is_logged to 0 (false)

EXEC sp_altermessage

@message_id = 17184

,@parameter = 'WITH_LOG'

,@parameter_value = 'false'

-- Do a Errorlog cycle

DBCC ERRORLOG

-- See, the change has been bypassed by SQL Serve

sp_readerrorlog 0,1,'The error log has been reinitialized'

EDIT: I have a ideia now ...change the is_event_logged is useless if the piece of code that calls the message uses a WITH LOG option or something similar. I tested now

RAISERROR (17184,10,1) WITH LOG

and it's logging normally...

If I think right, the question is about prevalence...