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