Question

You manage a SQL Server 2022 instance running on an Azure VM. Your team wants to be notified automatically whenever a fatal error occurs that indicates possible database corruption or hardware-related resource failures (system errors with severity levels 19 through 25). You need to configure SQL Server Agent so that an operator receives an email whenever any such error is raised. What is the most efficient way to accomplish this?

Accepted Answer

Create a single SQL Server Agent alert for each severity level 19 through 25, and configure each alert to notify the operator by email.

Answer

Create a SQL Server Agent job that queries the error log every minute and sends email using sp_send_dbmail when a matching error is found.

Answer

Create a WMI event alert that monitors the Windows Application log for any SQL Server error, and attach the operator as a notification.

Answer

Enable the default trace and configure a performance condition alert that triggers when the error count metric exceeds zero.