Improvement of SQL Server Database alarm

Consider turning on the database mail function for all SQL Server servers in the production environment, which is the mail sending function of the database. By configuring the access to the mail server, it can receive SQL Server event alarms in time.

SQL Server alarms send alarms based on the engine's event notification mechanism, and alarm notifications are sent when events occur.

At this stage, you need to collect the following two types of SQL Server event alarms:

Severity-event severity level 13 to 25 alarm

Error Number-includes operating system errors, IO errors, memory errors, AlwaysOn status related alarms.

Strategy adjustment

We consider integrating these alarms that need to be monitored into the application log and accessing the ELK log analysis system.

We learned that errors / messages generated by SQL Server and SQL Server applications will be sent to the Windows application log in the following situations:

Sys.messages errors with a severity level of 19 or higher

Any RAISERROR statement called using WITH LOG syntax

All sys.messages errors modified or created using sp_altermessage

All log events logged using xp_logevent

Quote from https://docs.microsoft.com/en-us/sql/ssms/agent/create-an-alert-using-severity-level"Severity levels from 19 through 25 send a SQL Server message to the Microsoft Windows application log and trigger an alert. Events with severity levels less than 19 will trigger alerts only if you have used sp_altermessage, RAISERROR WITH LOG, or xp_logevent to force them to be written to the Windows application log. "

For the Severity and Error Number alarms mentioned above that we need to monitor, we need to make the following changes:

Severity-after discussion, user-level errors below severity level 19 are not logged. Fatal Error at level 19 and above is logged to the application log by default, and we do not need to make any changes.

Error Number-errors that need to be monitored. According to the query results of the attachment, the is_event_logged field of some error numbers is 0, indicating that the application log is not logged. You can log to the application log by changing this field to 1 through the sp_altermessage above.