How to Set Up SQL Server Alerts and Know if SQL Server is Healthy [Quick Tip]

SQL Server alerts let you know what is happening on your instance. They let you become a proactive DBA and know about problems potentially before your users do. If you don’t have a monitoring tool and it isn’t in the budget right now, setting up and monitoring SQL Server alerts is a free and very quick solution for you to get visibility into problems in your environment as they happen. No extra license is required, no extra budget is required, just some time and knowledge. This is another one of our common findings when performing a SQL Server Health Assessment with a client – and it really shouldn’t be such a common finding.

Want to save yourself a “ding” on one of our reports? Want to get ahead of your problems?

Setup and Use SQL Server Alerts!

And become a happier DBA.

Essentially, a SQL Server alert is a reaction that you configure to “fire” on certain conditions. For example when a certain error message is raised or a performance condition is raised on the server. This functionality is built into the SQL Server Agent and there are several out of the box configuration options you can choose to alert on. When we perform an assessment – we are looking to see a few things:

• Are SQL Server Alerts configured? Do you have them configured at all? Or do you not have any alerts configured? (Sadly, this is often the default state)
• Are they configured correctly? Are you watching for the right conditions?
• Does someone actually get notified when they occur? More than once, we’ve seen SQL Server alerts configured but no one notified when they fire.

If you don’t have these configured, if you can’t answer those questions – then this post is for you. In about 15 minutes of videos, we’ll walk you through configuring them. Let’s see how easy it is to setup SQL Server alerts.

Configuring SQL Server Alerts

There are three main tasks to configuring alerts. We’ll describe each of these separately with a quick video showing how to set them up. These were all done in 15 minutes – and that was mostly because I was talking and demonstrating them. If you have 8-15 minutes to spare at some point today – you can have SQL Server alerts configured and properly firing to alert whoever is wearing the DBA hat of a problem. Follow along on a test system and see how simple this is.

First – Creating the SQL Server Alerts

The order of steps here doesn’t matter so much. Some do them differently but this is the order I typically do this. First up is creating the actual alerts.  I prefer to create alerts for severity levels 16 and above (to 25). And one for error number 825. In a nutshell, error level severity is what it sounds like – it describes how important (or severe) and error is. A severity 10 error message is typically a warning, for instance, while a severity 23 error message likely is indicating SQL Server corruption in a database file or files. Severity 16 is the start of the error messages we want to be alerted about typically.

Sometimes severity 16 messages, however, can be considered “acceptable” in some organizations and you may need to filter these out based on your environment. I like to start with severity 16 included always because there are some pretty important messages here.

Error 825 is a special case – this is raised as a severity 10 error – it is simply a warning. But as Paul Randal blogs about it is a pretty serious warning. I want to know when these warnings pop up before they turn into an error message with a higher severity.

Below is the script to configure your SQL Server alerts as demonstrated in the video.

EXEC msdb.dbo.sp_add_alert @name=N'Severity 16 Error', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 17 Error', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 18 Error', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 19 Error', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 20 Error', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 21 Error', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 22 Error', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO



GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 23 Error', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 24 Error', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 25 Error', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 825', 
		@message_id=825, 
		@severity=0, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=0, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO

Next – Create an Operator to Receive SQL Alerts

The SQL Agent alerts here are good to have. But you want them to actually go to someone! Whoever wears the hat of SQL Server DBA in your organization should receive the alert. I prefer to set this up as an e-mail distribution list always – so more than one person can receive them for backup and for an extra set of eyes.

This video demonstrates how to create an operator in SQL Server agent and subscribe to your SQL alerts.

This is the script to create the operator and subscribe to alerts as seen in the video.

USE [msdb]
GO

/****** Object:  Operator [DBA_Alerts]    Script Date: 9/28/2014 10:50:41 PM ******/
EXEC msdb.dbo.sp_add_operator @name=N'DBA_Alerts', 
		@enabled=1, 
		@pager_days=0, 
		@email_address=N'DBA_Alerts@linchpinpeople.com'
GO


EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 16 Error', @operator_name=N'DBA_Alerts', @notification_method = 1
GO

-- Continue

 Finally – Setup Database Mail

Otherwise the operator won’t receive the e-mail about the alerts firing. Why go through all the above steps if you don’t want the alerts to actually reach anyone’s inbox?

This video demonstrates how to configure database mail in SQL Server. Substitute your own mail server information.

Happy Alerting!

That’s it. Configure SQL Server alerts, set up operators and make sure they can get the e-mails. This blog post likely took longer to type and edit than it will for you to click to your SQL Server instances and set this up. Be careful, though, we often find that once you start getting these alerts you risk uncovering problems that have existed all along in your instance!