Introduction

A production DBA shouldn’t have to discover a problem only after a user reports it.

Imagine these situations:

• A critical SQL Server error occurs at 2 AM.
• A transaction log suddenly fills up.
• A backup job fails.
• A database starts experiencing repeated errors.
• A critical SQL Server Agent job fails.
• A performance counter crosses a dangerous threshold.

If nobody is watching, the DBA may discover the problem much later.

This is where SQL Server Alerts can help.

SQL Server Agent can monitor SQL Server events, performance conditions and WMI events and can respond by notifying an operator or running a job. Microsoft Learn

The goal of this article is:

Understand what should be monitored, configure useful alerts, avoid alert noise, and make sure the right person receives the notification.

What is a SQL Server Alert?

A SQL Server Agent alert is an automated response to a defined event or condition.

The basic flow is:

SQL Server Event / Condition
          ↓
    SQL Server Agent
          ↓
        Alert
          ↓
   ┌──────┴──────┐
   ↓             ↓
Notify DBA    Run Job

For example:

Severity 20 Error
       ↓
SQL Server Agent Alert
       ↓
Email DBA

SQL Server Agent supports three main alert types:

1. SQL Server event alerts
2. Performance condition alerts
3. WMI event alerts Microsoft Learn

For most DBA environments, SQL Server event alerts and carefully selected performance alerts are the most useful starting points.

Why Do We Need SQL Server Alerts?

Without alerts:

Problem occurs
      ↓
Nobody notices
      ↓
Application starts failing
      ↓
User reports problem
      ↓
DBA starts investigation

With appropriate alerting:

Problem occurs
      ↓
SQL Server Agent detects it
      ↓
Alert fires
      ↓
DBA receives notification
      ↓
Investigation starts earlier

The objective isn’t to create hundreds of alerts.

The objective is:

Detect important problems early enough to take action.

What Should You Monitor?

I would divide SQL Server alerts into five categories.

1. Critical SQL Server Errors

Monitor serious errors that indicate possible database or instance-level problems.

Examples include:

• Severity 19
• Severity 20
• Severity 21
• Severity 22
• Severity 23
• Severity 24
• Severity 25

Microsoft documents severity 19–25 as serious errors written to the SQL Server error log; severity 20–24 represent fatal errors affecting the current task and potentially the connection. Microsoft Learn

A practical production strategy is to have alerts for critical severity levels, but don’t blindly create alerts for every severity without understanding the expected behavior in your environment.

2. Specific SQL Server Errors

Sometimes an error number is more useful than a general severity.

For example, you might want an alert for a specific error associated with an important production condition.

The alert can be restricted to:

• Error number
• Database
• Message text

SQL Server Agent supports filtering SQL Server events using error number, severity, database and message text. Microsoft Learn

3. Performance Conditions

SQL Server Agent can also monitor performance counters.

For example:

SQLServer:Locks
SQLServer:SQL Statistics
SQLServer:Buffer Manager
SQLServer:Databases

An alert can be configured to fire when a counter:

Rises above
Falls below
Becomes equal to

a specified value. Microsoft Learn

However:

Don’t turn every performance metric into an alert.

A threshold that looks alarming in one environment may be completely normal in another.

4. SQL Server Agent Job Failures

This is one of the most useful operational notifications.

Imagine your backup job fails:

Backup Job
    ↓
FAIL
    ↓
No notification
    ↓
Next backup also fails
    ↓
Recovery point becomes older

A better design is:

Backup Job
    ↓
FAIL
    ↓
SQL Agent notification
    ↓
DBA

SQL Server Agent supports job completion notifications through operators. Microsoft Learn

For critical jobs, I recommend configuring notifications for failure at minimum.

5. Windows Management Instrumentation (WMI) Events

SQL Server Agent also supports WMI event alerts.

These can be useful for specific Windows or SQL Server events, but they are more advanced and should generally be introduced after your basic SQL Server event and job-failure alerting is working.

The Alerts I Would Start With

For a production SQL Server, I would start with a relatively small set:

Alert	Priority	Why
Severity 19	High	Serious resource/engine condition
Severity 20	Critical	Fatal error
Severity 21	Critical	Serious database/instance issue
Severity 22	Critical	Integrity-related issue
Severity 23	Critical	Database integrity issue
Severity 24	Critical	Hardware/media-related issue
Severity 25	Critical	System-level/fatal error
Important error numbers	High	Specific known production conditions
Backup job failure	Critical	Recovery risk
CHECKDB failure	Critical	Database integrity risk
Important maintenance job failure	High	Operational risk
Selected performance thresholds	Medium/High	Early warning

This is a starting framework, not a universal alert list.

Before Creating Alerts: Define the Response

An alert without a response is often just noise.

For every alert, answer:

Who receives it?

What should they do?

How quickly should they respond?

For example:

Alert	Notification	Expected Action
Severity 20	DBA immediately	Investigate SQL Server error
Backup failure	DBA	Check backup job/storage
CHECKDB error	DBA + recovery team	Investigate integrity
Disk-space warning	DBA/Infra	Increase or free capacity
Job failure	DBA	Investigate failed step

This turns alerting into an operational process rather than simply sending emails.

Configuring SQL Server Alerts

There are two common ways to configure alerts:

Using SSMS

In SQL Server Management Studio:

SQL Server
   ↓
SQL Server Agent
   ↓
Alerts
   ↓
New Alert

You can configure the alert type, event/severity/performance condition and response. Microsoft Learn

Using T-SQL

T-SQL is useful when you want repeatable configuration across multiple SQL Server instances.

Step 1: Create an Operator

An operator is the person or group that receives SQL Server Agent notifications. Operators are aliases for notification recipients; they aren’t SQL Server security principals. Microsoft Learn

Example:

USE msdb;
GO

EXEC dbo.sp_add_operator
    @name = N'DBA Team',
    @enabled = 1,
    @email_address = N'dba-team@example.com';
GO

The sp_add_operator procedure creates an operator for use with alerts and jobs. Microsoft Learn

Replace the email address with your organization’s distribution list.

Important

Don’t put an individual’s personal email address into every alert.

A production environment is usually better served by a distribution group such as:

dba-team@company.com

This reduces the risk of alerts disappearing when someone is on leave or changes teams.

Step 2: Configure Database Mail

SQL Server Agent uses Database Mail for email notifications. Microsoft documents Database Mail as the mail system used for SQL Server Agent email notifications. Microsoft Learn

The exact Database Mail configuration depends on your SMTP environment.

After configuring it, test it independently before troubleshooting SQL Agent alerts.

For example:

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = N'DBAProfile',
    @recipients = N'dba-team@example.com',
    @subject = N'SQL Server Database Mail Test',
    @body = N'This is a test message from SQL Server.';
GO

If this doesn’t work, fix Database Mail first.

Don’t troubleshoot the SQL Server Agent alert itself until the underlying email path works.

Step 3: Configure SQL Server Agent to Use Database Mail

In SSMS:

SQL Server Agent
    ↓
Properties
    ↓
Alert System
    ↓
Enable mail profile

Select:

Mail System = Database Mail

Microsoft notes that SQL Server Agent Mail is not enabled by default, and changing the mail system requires restarting SQL Server Agent for the change to take effect. Microsoft Learn

Step 4: Create a Severity Alert

For example, let’s create an alert for severity 20.

USE msdb;
GO

EXEC dbo.sp_add_alert
    @name = N'DBA - Severity 20',
    @severity = 20,
    @enabled = 1,
    @description = N'Alert when a severity 20 SQL Server error occurs.';
GO

Then connect the alert to the operator:

EXEC dbo.sp_add_notification
    @alert_name = N'DBA - Severity 20',
    @operator_name = N'DBA Team',
    @notification_method = 1;
GO

Here:

1 = E-mail

Microsoft documents that severity-based alerts can be created using sp_add_alert, and by default execution of sp_add_alert requires sysadmin. Microsoft Learn

What Severity Should I Monitor?

A useful starting point is:

19 → High
20 → Critical
21 → Critical
22 → Critical
23 → Critical
24 → Critical
25 → Critical

But don’t interpret severity alone as a complete diagnosis.

An alert means:

Something important happened.

It doesn’t mean:

We already know the root cause.

Step 5: Create an Alert for a Specific Error

You can also monitor a specific SQL Server error number.

Example structure:

USE msdb;
GO

EXEC dbo.sp_add_alert
    @name = N'DBA - Error 12345',
    @message_id = 12345,
    @enabled = 1,
    @description = N'Alert for SQL Server error 12345.';
GO

EXEC dbo.sp_add_notification
    @alert_name = N'DBA - Error 12345',
    @operator_name = N'DBA Team',
    @notification_method = 1;
GO

Replace 12345 with the actual error number you want to monitor.

Important

Don’t copy an error number from a random blog and create an alert without understanding it.

First verify:

• What does the error mean?
• Is it expected?
• How frequently does it occur?
• Is it actionable?
• What should the DBA do when it occurs?

Step 6: Restrict an Alert to a Database

You can also associate a SQL Server event alert with a specific database.

For example:

EXEC dbo.sp_add_alert
    @name = N'SalesDB - Important Error',
    @message_id = 12345,
    @database_name = N'SalesDB',
    @enabled = 1,
    @description = N'Monitors error 12345 for SalesDB.';
GO

This can prevent an alert from firing for the same event in unrelated databases.

SQL Server Agent supports database filtering in SQL Server event alerts. Microsoft Learn

Step 7: Configure Job Failure Notifications

For a critical SQL Agent job, configure email notification.

For example:

USE msdb;
GO

EXEC dbo.sp_update_job
    @job_name = N'DBA - Daily SalesDB Backup',
    @notify_level_email = 2,
    @notify_email_operator_name = N'DBA Team';
GO

The important value here is:

2 = Notify on failure

So the flow becomes:

Backup Job
     ↓
Fails
     ↓
SQL Server Agent
     ↓
DBA Team
     ↓
Investigation

This is often more useful than creating a generic alert for every possible backup-related error.

Performance Alerts: Use Carefully

SQL Server Agent can create alerts based on performance counters.

For example, you might configure an alert when a particular counter rises above a threshold.

The configuration includes:

Object
Counter
Instance
Condition
Threshold

Microsoft documents this model for performance condition alerts. Microsoft Learn

But here’s the important part:

Don’t simply say:

CPU > 80% = alert

and assume the problem is SQL Server.

You could have:

SQL Server CPU
       +
Antivirus
       +
Backup software
       +
Monitoring agent
       ↓
High server CPU

Alert vs Monitoring

These are not the same thing.

Alert

Something crossed a predefined condition
       ↓
Notify me

Monitoring

Continuously collect
CPU
Memory
I/O
Waits
Queries
Blocking
Storage
       ↓
Analyze trends

Alerts are therefore only one part of a monitoring strategy.

Avoid Alert Fatigue

One of the biggest problems with poorly designed alerting is too many notifications.

Imagine receiving:

200 alerts/day

After a few weeks, people stop taking them seriously.

That’s dangerous.

A good alert should be:

Actionable

Someone should know what to do.

Relevant

It should represent a meaningful condition.

Reliable

It shouldn’t constantly fire because of normal workload.

Prioritized

Critical problems should look different from warnings.

Bad Alert

CPU > 70%

with no context.

Better Alert

Production SQL Server CPU > 90%
for sustained workload

with a clear owner and investigation procedure.

Even better:

The alert is part of a monitoring system that can distinguish SQL Server CPU from overall server CPU and provides enough context for investigation.

Don’t Alert on Everything

Before creating an alert, ask:

Will someone take action?
        ↓
      YES
        ↓
Create alert

If:

Nobody will act

then consider monitoring it instead of generating an alert.

Testing Your Alerts

Never assume an alert works because it exists in SSMS.

Test the complete chain:

Event
 ↓
SQL Server
 ↓
SQL Server Agent
 ↓
Alert
 ↓
Operator
 ↓
Email
 ↓
DBA receives notification

Check:

• Alert enabled?
• SQL Server Agent running?
• Database Mail working?
• Correct operator?
• Correct notification method?
• Correct severity/error?
• Email actually received?
• Correct database filter?
• Alert appears in SQL Agent history/logs?

A Simple Testing Strategy

Don’t test critical production errors by intentionally damaging production.

Instead:

1. Configure the alert.
2. Verify the Database Mail path separately.
3. Use a controlled test event where appropriate.
4. Confirm the operator receives the notification.
5. Document the test.

SQL Server Alert Configuration Checklist

Use this checklist before putting an alert into production:

Alert has a clear name

Alert is enabled

Correct alert type selected

Correct error/severity/condition configured

Database filter verified

Operator created

Database Mail configured

SQL Server Agent configured for email

Notification assigned

Test completed

DBA knows what action to take

Alert priority documented

Alert reviewed periodically

Permissions You Need

This is particularly important when implementing alerts in a production environment.

Creating alerts isn’t simply a normal database-level operation.

Microsoft documents that sp_add_alert requires sysadmin by default. Microsoft Learn

SQL Server Agent administration also has its own security model involving msdb roles such as:

SQLAgentUserRole
SQLAgentReaderRole
SQLAgentOperatorRole

with different levels of access. Microsoft Learn

Therefore, if you’re a developer or support engineer who doesn’t have the required permission:

Don’t ask for sysadmin automatically.

Instead, involve the DBA team and explain:

What alert needs to be created
Why it is required
What error/condition it monitors
Who should receive it

The DBA/security team can then provide the appropriate access or configure the alert.

Version & Platform Notes

This article primarily applies to SQL Server Database Engine environments, including SQL Server 2019, SQL Server 2022 and SQL Server 2025.

SQL Server Agent supports alerts on SQL Server, but platform differences matter.

For example, Microsoft documents that Azure SQL Managed Instance supports most, but not all, SQL Server Agent features. Microsoft Learn

Don’t assume that a SQL Server Agent alert configuration designed for a traditional SQL Server instance can simply be copied to:

• Azure SQL Database
• Azure SQL Managed Instance
• SQL Server on Linux
• SQL Server Express

Check the capabilities of the specific platform first.

A Practical Alerting Framework

For a production SQL Server, I would think about alerting in layers.

Layer 1 – Critical

Notify immediately.

Severity 20–25
Critical database errors
Critical backup failure
CHECKDB failure

Layer 2 – High

Investigate quickly.

Important SQL errors
Critical job failures
Storage/capacity warnings
Selected performance thresholds

Layer 3 – Warning

Monitor and investigate trends.

Increasing job duration
Capacity approaching threshold
Selected performance conditions

Layer 4 – Informational

Usually don’t generate immediate alerts.

Normal maintenance
Expected events
Routine information

This helps prevent the DBA team from treating every notification as a production emergency.

Example: Production Backup Alert

Let’s put everything together.

Suppose you have:

Daily Full Backup
        ↓
SalesDB
        ↓
SQL Agent Job

Configure:

Job failure
     ↓
DBA Team
     ↓
Email

T-SQL:

USE msdb;
GO

EXEC dbo.sp_add_operator
    @name = N'DBA Team',
    @enabled = 1,
    @email_address = N'dba-team@example.com';
GO

EXEC dbo.sp_update_job
    @job_name = N'DBA - Daily SalesDB Backup',
    @notify_level_email = 2,
    @notify_email_operator_name = N'DBA Team';
GO

Now:

Backup succeeds
       ↓
Nothing urgent

Backup fails
       ↓
Email DBA
       ↓
Investigate immediately

That’s a useful alert.

Example: Critical SQL Server Error

USE msdb;
GO

EXEC dbo.sp_add_alert
    @name = N'DBA - Severity 20',
    @severity = 20,
    @enabled = 1,
    @description = N'Critical SQL Server severity 20 error.';
GO

EXEC dbo.sp_add_notification
    @alert_name = N'DBA - Severity 20',
    @operator_name = N'DBA Team',
    @notification_method = 1;
GO

The result:

Severity 20 error
       ↓
SQL Server Agent
       ↓
Alert
       ↓
DBA Team
       ↓
Investigation

Common Mistakes

Creating too many alerts

More alerts don’t necessarily mean better monitoring.

No notification configured

An alert nobody receives isn’t useful.

No action plan

The DBA gets an email but doesn’t know what to investigate.

Using inappropriate thresholds

A threshold copied from another environment may create constant false alarms.

Not testing the email path

The alert may work while Database Mail doesn’t.

Giving everyone sysadmin

Use the appropriate security model instead.

Treating an alert as root-cause analysis

An alert tells you that something happened.

It doesn’t necessarily tell you why it happened.

Ignoring version/platform differences

SQL Server, Azure SQL Database and Azure SQL Managed Instance don’t provide identical capabilities.

The Most Important Principle

I would summarize SQL Server alerting with this:

Don’t create an alert simply because SQL Server can detect an event. Create an alert when detecting that event early can help someone take meaningful action.

A good alert should answer three questions:

What happened?

Who needs to know?

What should they do next?

If your alert doesn’t answer those questions, it probably needs to be redesigned.

SQL Server Alert Cheat Sheet

Requirement	Recommended Approach
Critical SQL error	Severity/error-number alert
Specific known error	Error-number alert
Database-specific error	Error + database filter
Performance threshold	Performance condition alert
Backup failure	SQL Agent job notification
Maintenance job failure	SQL Agent notification
Windows/SQL event	WMI alert where appropriate
Email notification	Database Mail + Operator
Alert automation	Alert can notify operator or run a job
Too many alerts	Review thresholds and actionability
No permission	Involve DBA/security team
Azure SQL Database	Don’t assume SQL Agent features exist
Azure SQL MI	Verify supported Agent features

Summary

SQL Server alerts are not a replacement for monitoring.

They are the early-warning system that connects an important event with the person or automated process that needs to respond.

The goal shouldn’t be:

“How many alerts can we configure?”

It should be:

“How early can we detect an important problem and get it to the right person?”

That’s the difference between having alerts and having a useful production monitoring strategy.

And just like with database maintenance, always document three things before implementing an alert:

SQL Server version/platform → Required permission → Expected action

That small discipline can save a lot of time when the alert actually fires at 2 AM.