Force RaiseError for selected sysmessages

Question

Force RaiseError for selected sysmessages

N.D

Ten Centuries

Points: 1132
More actions
May 24, 2012 at 10:14 am

#392339

Not a very succint title, wan't sure how to put it ... basically, our monitoring team are putting together a module to scrape the event log for SQL errors
I have a list of the error numbers I want to look for from sysmessages and have confirmed that they would get written to the event log.
However, they need an example of each message I want to scrape for, to be raised as an error in the event log in order that they can configure whatever it is that they're making!
Is there a way I can force each of the selected events in sysmessages to write itself out to the error log? specifics like database name are not necessary. I think I need to look at RaiseError, but not too sure where to start 😛

Viewing 9 posts - 1 through 8 (of 8 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 1

IIRC, the decision to log something is from the RaiseError command, not the error. If the WITH LOG parameter is used, it's logged. If the severity is high enough, this parameter is required.

http://msdn.microsoft.com/en-us/library/ms178592.aspx

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 2

Just to add to what Steve said, you can see which system errors are automatically logged:

SELECT *

FROM sys.messages

WHERE is_event_logged = 1

AND language_id = 1033

ORDER BY message_id;

Nitpick: sysmessages is deprecated, use sys.messages

If you want to create a new error message that is logged whenever raised you can do so like this:

EXEC sys.sp_addmessage

@msgnum = 50001,

@severity = 16,

@msgtext = N'Hello!',

@with_log = 'true';

RAISERROR(50001,-1,-1)

And you'll see two sequential entries like this in the error log:

Error: 50001, Severity: 16, State: 1.
Hello!

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

N.D Ten Centuries Points: 1132 More actions · Answer 3

Thanks both,

To clarify, it's the non-custom messages I'm trying to get to write to the log, eg - a 9002 log full event without having to force the log to become full. The code snippet above is what I used to get a list of the logged messages from sysmessages.

I tried a Raiserror on 9002 & 3041 & got the following:

Msg 2732, Level 16, State 1, Line 1

Error number 9002 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000

So I'm guessing that it can only be used on custom messages & a subset of those in sysmessages.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 4

N.D (5/25/2012)
Thanks both,
To clarify, it's the non-custom messages I'm trying to get to write to the log, eg - a 9002 log full event without having to force the log to become full. The code snippet above is what I used to get a list of the logged messages from sysmessages.
I tried a Raiserror on 9002 & 3041 & got the following:
Msg 2732, Level 16, State 1, Line 1
Error number 9002 is invalid. The number must be from 13000 through 2147483647 and it cannot be 50000
So I'm guessing that it can only be used on custom messages & a subset of those in sysmessages.

We are not allowed to raise those errors as they belong to the database engine.

Another option is to use Alerts to do something specific for those errors you're interested in watching.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

Steve Jones - SSC Editor SSC Guru Points: 736162 More actions · Answer 5

I would lean towards alerts here, as opc.three suggested.

DBANewbie Hall of Fame Points: 3095 More actions · Answer 6

Sorry to hijack this thread but i have a very similar requirement.

I have SQL Server agent alerts set up to notify me of errors with a severity of 16 and upwards; but these are only emailed out if the error is also written to the event log.

So i thought i would modify the "Is_Event_Logged" column within the sys.messages table for all those errors i would like to be notified of; but i get an "Ad hoc updates to system catalogs are not allowed" message when i try to do this.

I have read that i may be able to get around this using the DAC but i wanted to check if this is a generally acceptable way of achieving what i want or if i should just put up with the fact that i cannot be alerted of all errors as i would like.

Thanks in advance.

Orlando Colamatteo SSC Guru Points: 182279 More actions · Answer 7

Check out system stored procedure sys.sp_altermessage.

There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato

DBANewbie Hall of Fame Points: 3095 More actions · Answer 8

DBANewbie

Hall of Fame

Points: 3095

June 13, 2014 at 6:46 am

#1721443

Thanks very much, that has worked perfectly.