Scenario
I ran a couple of health checks on my SQL Servers and the checks indicated I didn’t have critical alerts configured. What do I need to do?
Solution
SQL Server has alerts that get more important based on the severity of the alert. Anything of severity 16 or below tends to refer to the database and deals with issues that are tied to syntax errors, violations of foreign keys, etc. While those errors are typically important, they don’t refer to anything with regards to overall health of the SQL Server. Alerts 17 through 25 do. Those are the ones your health checks are probably firing on. So what are these alerts?
|
Severity Level |
Meaning |
| 17 | Insufficient Resources |
| 18 | Nonfatal Internal Error Detected |
| 19 | SQL Server Error in Resource |
| 20 | SQL Server Fatal Error in Current Process |
| 21 | SQL Server Fatal Error in Database (dbid) Process |
| 22 | SQL Server Fatal Error Table Integrity Suspect |
| 23 | SQL Server Fatal Error: Database Integrity Suspect |
| 24 | Hardware Error |
| 25 | (no description) |
As you might guess, these are errors you want to alert on because they either represent a resource issue, an integrity issue, or a hardware issue. Here’s what you’ll need to do to setup alerts on your SQL Servers. While you can do this via the GUI, if you have everything scripted, you can quickly run the scripts for any SQL Server you need to set up.
Configuring SQL Server Database Mail
In order to be able to receive emails when an alert fires, you need to set up database mail. You’re going to need a few bits of information:
- The email address to use. This can be a “dummy” address, but you should have a good reply to email address.
- The mail server to connect to.
- Any authentication requirements for the mail server.
Here’s a generic configuration. Note that I’m using a dummy address to send from, but the reply address should go back to the DBAs. I’ve specified these values as variables in the TODO section so that I can re-use the script and only make a couple of changes depending on the environment.
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE
GO
DECLARE @pid INT;
DECLARE @acctid INT;
EXEC msdb.dbo.sysmail_add_profile_sp
@profile_name = 'Default Profile',
@profile_id = @pid OUTPUT;
EXEC msdb.dbo.sysmail_add_account_sp
@account_name = 'Default Account',
@email_address = 'sql.myserver@mycompany.com',
@display_name = 'SQL - myserver',
@replyto_address = 'DBAs@mycompany.com',
@mailserver_name = 'smtp.mycompany.com',
@account_id = @acctid OUTPUT;
EXEC msdb.dbo.sysmail_add_profileaccount_sp
@profile_id = @pid,
@account_id = @acctid,
@sequence_number = 1;
GO
Setting Up SQL Server Agent Alerts
With Database Mail configured, it’s time to set up the alerts. Here’s a simple, default script. If you’re worried about a lot of alerts firing all at once, you can set a delay by changing the @delay_between_responses parameter. Note that I am setting the @include_event_description_in parameter to 1, meaning however the alert sends to an operator, the details of the alert will be included. Since we’ll be using email, that means the details of why the alert fired will be included in the email message body.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 17 Alert',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 18 Alert',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 19 Alert',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 20 Alert',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 21 Alert',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 22 Alert',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 23 Alert',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 24 Alert',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error 25 Alert',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1;
GO
You should see the list of alerts:
Checking the properties on a particular alert, you should see the severity matches the name:
Configuring the SQL Server Operator
The alert will need to go somewhere. That’s what configuring an Operator is for. Here’s a generic operator for DBAs. Do note that the name of the operator and the email are being set by variables. Again, this is to be able to re-use the script across multiple servers/environments. It does appear a bit lengthy, but that’s because you have to associate a notification with each alert.
USE msdb;
GO
EXEC msdb.dbo.sp_add_operator
@name = 'DBAs',
@enabled = 1,
@email_address = 'DBAs@mycompany.com';
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 17 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 18 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 19 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 20 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 21 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 22 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 23 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 24 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
EXEC msdb.dbo.sp_add_notification
@alert_name = N'Error 25 Alert',
@operator_name = 'DBAs',
@notification_method = 1;
GO
Once the script is run you should see the Operator listed and you can check its properties to ensure the email address is set.
Also, you can verify that all the alert notifications are configured.
Verify SQL Server Agent Status
One last thing: make sure SQL Server Agent is stared and configured to start automatically. SQL Server Agent is what drives this alerting. If it’s not running and you do have an issue, you won’t get the email. Therefore, check your SQL Server Agent configuration closely.
Thanks for reading this article,
Next steps :
- Add this script to your database toolkit
- Share this with your colleagues because Sharing is Learning
- Comment below if you need any assistance
Powered by CodeReview – Let’s make it Better!