How to add a user-defined SQL Server error message and send message to the Windows event log

13 September,2017 by Jack Vamvas

I discussed in an earlier post on triggering an error message to write to the Windows event viewer. This can be very useful - as log readers can  use one source to gather error messages and trigger alerts.

If you're managing large amounts of SQL Server instances - standardising is essential. One of the standardisations is to add user-defined error messages dedicated to the DBA team. These user-defined messages can be used to give information for alerts - for example - failed sql server agent jobs , failed ETL jobs etc

An example of how you could manage this process - is to add 4 (or as many as required) ids.

As part of the standard SQL Server Installation package for SQL Server 2016  this code is executed. The code adds 4 entries into the sys.messages view.
When the dedicated  monitoring system  scans the Windows event viewer , it includes a check for these error messages i.e event ids: 55000 , 60000, 70000 and 75000.


EXEC sp_addmessage 55000, 16, 'DBAERROR: %s', 'english', false,replace
EXEC sp_addmessage 60000, 16, 'DBAWARNING: %s', 'english', false,replace
EXEC sp_addmessage 75000, 16, 'DBAERROR: %s', 'english', false,replace
EXEC sp_addmessage 70000, 16, 'DBAWARNING: %s', 'english', false,replace

 

To  exploit these messages as part of code this is an example. This example will add the message 'DBAERROR:A message from MSSQL$TEST' to the Windows event application viewer.

EXEC xp_logevent 60000, 'A message from MSSQL$TEST', error

 

Read more on  How to write to a sql error to windows server event viewer

 

 


Author: Jack Vamvas (http://www.sqlserver-dba.com)


Share:

Verify your Comment

Previewing your Comment

This is only a preview. Your comment has not yet been posted.

Working...
Your comment could not be posted. Error type:
Your comment has been posted. Post another comment

The letters and numbers you entered did not match the image. Please try again.

As a final step before posting your comment, enter the letters and numbers you see in the image below. This prevents automated programs from posting comments.

Having trouble reading this image? View an alternate.

Working...

Post a comment on How to add a user-defined SQL Server error message and send message to the Windows event log


sqlserver-dba.com | SQL Server Performance Tuning | SQL Server DBA:Everything | FAQ | Contact|Copyright & Disclaimer