Why are some high severity error message in SQL Server not logged to the Windows Event Log?

We are running SQL Server 2016 enterprise and I was doing some troubleshooting to see why none of my alerts were working (turns out someone had added the -n parameter to the SQL Server startup). During the troubleshooting, I noticed that a number of the built in messages with high severity (>= 18) are not set to log to the event log.

use master;

select * from sys.messages 
where severity >= 18 and language_id = 1033 and is_event_logged = 0

message_id
language_id
severity
is_event_logged
text

21
1033
20
0
Warning: Fatal error %d occurred at %S_DATE. Note the error and time, and contact your system administrator.

565
1033
18
0
A stack overflow occurred in the server while compiling the query. Please simplify the query.

613
1033
21
0
Could not find an entry for worktable rowset with partition ID %I64d in database %d.

669
1033
22
0
The row object is inconsistent. Please rerun the query.

683
1033
22
0
An internal error occurred while trying to convert between variable-length and fixed-length decimal formats. Run DBCC CHECKDB to check for any database corruption.

684
1033
22
0
An internal error occurred while attempting to convert between compressed and uncompressed storage formats. Run DBCC CHECKDB to check for any corruption.

685
1033
22
0
An internal error occurred while attempting to retrieve a backpointer for a heap forwarded record.

686
1033
22
0
The maximum level of the B-Tree for rowset %I64d has been reached. SQL Server only supports upto 255 levels.

831
1033
20
0
Unable to deallocate a kept page.

etc.

From what I understand, for alerts to work, the messages must be logged to the Windows application event log. I haven't looked through all of them but many seem to the sort of things you would want to be alerted to, so why are all these messages (there are almost 100 of them) not logged?

Basically, the value in the is_event_logged column is not to be trusted.

I wish that SQL Server would blindly following the values in sys.messages when generating an error. But for instance to get rid of "successful backup" messages in the logs you have to start SQL Server using trace flag 3226 instead of configuring those messages in sys.messages (using sp_altermessage).

Take for instance error 831, one of the errors in your list. This and also this suggest that error 831 is indeed written to the logs.

In other words: My guess is that most of those severe messages you list are indeed hardwired to be written to the logs, even though sys.messages say something else.

In order to be conclusive, we would have to repro each of those error and see for ourselves. These errors are by nature difficult to repro. One can of course suggest to MS to be more consistent with how they adhere to is_event_logged in sys.messages, but I have a feeling that they have other things with higher priority and we just have to live with this.

StackExchange Database Administrators Q#328727, answer score: 10