4

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?

2

1 Answer 1

10

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.

3
  • Thanks. I was hoping the answer would be something better than "Microsoft is sloppy" but oh well.
    – Caynadian
    Jun 29, 2023 at 15:29
  • Hi @Tibor, does is_event_logged mean it will be logged in to SQL Server errorlog or Windows's event log? Nov 23, 2023 at 16:05
  • In mot cases it means both. But there might be exceptions... Nov 23, 2023 at 21:40

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.