How do I: Alert on SQL Errors that aren’t logged to the windows event log

This is one of those common questions that if you ask a SQL DBA they will probably know the answer, but it is less common information within the SCOM community.

First if you want to get a sense of all the errors that SQL can generate to its own internal logs run the following from your server (Language ID will of course vary):

01

For my SQL 2014 Server I am getting back 11548 rows of messages:

02

 

Column name Data type Description
message_id int ID of the message. Is unique across server. Message IDs less than 50000 are system messages.
language_id smallint Language ID for which the text in text is used, as defined in syslanguages. This is unique for a specified message_id.
severity tinyint Severity level of the message, between 1 and 25. This is the same for all message languages within a message_id.
is_event_logged bit 1 = Message is event-logged when an error is raised. This is the same for all message languages within a message_id.
text nvarchar(2048) Text of the message used when the corresponding language_id is active.

For the most part the SQL MP’s will give you access to any of the events you might care about in both the SQL and Windows Application event logs. In those cases where this doesn’t happen there is a built in stored procedure in SQL that lets you write SQL errors to the Windows Application log to allow you to pick it up in other systems like SCOM.

sp_altermessage

If you dive into the code for the SQL replication MP’s you will find that this is how replication monitoring is implemented in SCOM. A series of sp_altermessage commands for different replication errors to turn on logging to the app log. Followed by corresponding event ID targeted alert generating rules.

03

https://msdn.microsoft.com/en-us/library/ms175094.aspx

The effect of sp_altermessage with the WITH_LOG option is similar to that of the RAISERROR WITH LOG parameter, except that sp_altermessage changes the logging behavior of an existing message. If a message has been altered to be WITH_LOG, it is always written to the Windows application log, regardless of how a user invokes the error. Even if RAISERROR is executed without the WITH_LOG option, the error is written to the Windows application log.

If a message is written to the Windows application log, it is also written to the Database Engine error log file.

The contents of this site are provided “AS IS” with no warranties, or rights conferred. Example code could harm your environment, and is not intended for production use. Content represents point in time snapshots of information and may no longer be accurate. (I work @ MSFT. Thoughts and opinions are my own.)