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):
For my SQL 2014 Server I am getting back 11548 rows of messages:
|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.
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.
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.