Question

You manage a SQL Server 2022 instance on an Azure VM. A nightly SQL Server Agent job that runs a critical ETL package occasionally fails, and the on-call DBA team is not being alerted. You need to ensure that the responsible DBA receives an email whenever this specific job fails, with minimal configuration changes. Database Mail is already configured and functional. What should you do?

Accepted Answer

Create an operator with the DBA's email address, then on the job's Notifications page set 'E-mail' to that operator 'When the job fails'.

Answer

Create a SQL Server Agent alert on error severity 16 and associate it with the operator, without changing the job.

Answer

Enable the 'Write to the Windows Application event log' option on the job's Notifications page 'When the job fails'.

Answer

Configure a WMI alert that responds to the job completion event and runs a T-SQL job to send an email.