As usual, the scripts in this post can be found HERE on our Git Hub repo
If your SQL Server comes across something that it’s not happy about, it really wants you to know so it’ll pop an entry into the error log.
First question of the day, who monitors their error log carefully?
Second question, if the answer to the above was yes (and be honest…) how often are you checking the error log?
The chances are, if something serious happens to SQL you’re going to want to know about it now and not tomorrow morning when you next give the error log an eyeball. So wouldn’t it be great if we could get SQL to tell us about these sorts of things straight away?
Well, you’ll be happy to learn that we can, we can do it by setting up SQL Alerts.
When SQL complains about something, it’ll usually assign the error with a severity number. I’m generally worried about anything that’s severity 17 or above.
Check out THIS page from Microsoft for an explaination of what the various severity codes mean.
Setting up the alerts is simple, we can use the proc sp_add_alert to do this. The following code will create an alert category for ‘High Severity Errors’ and configure alerts for each severity code, 17 and above.
USE [msdb]
GO
--Create High Severity Error category
EXEC sp_add_category
@class = N'ALERT',
@type = N'NONE',
@name = N'High Severity Error'
GO
--Configure alerts
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 17 Error:',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 18 Error: Nonfatal Internal Error',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 19 Error: Fatal Error in Resource',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 20 Error: Fatal Error in Current Process',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 21 Error: Fatal Error in Database Process',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 22 Error Fatal Error: Table Integrity Suspect',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 23 Error: Fatal Error Database Integrity Suspect',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 24 Error: Fatal Hardware Error',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
EXEC msdb.dbo.sp_add_alert @name=N'URGENT: Severity 25 Error: Fatal Error',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@category_name=N'High Severity Error'
GO
So, it’s all well and good that we’ve got alerts setup, they’re no good unless we tell SQL where to send notifications.
For the next section you’re going to need to make sure that you’ve got database mail configured on your server. I’m not going to go into that here but if you do need to know how to do it, there are plenty of websites that’ll take you through the process.
With database mail up and running, the next step is to setup an operator if you don’t already have one on your system. This can be done using, sp_add_operator.
The following script will configure your operator, be sure to change the name and email address to suit your own environment.
–Configure operator
USE msdb;
GO
EXEC msdb.dbo.sp_add_operator
@name = 'SQLUndercoverDBAs',
@enabled = 1,
@email_address = 'alerts@sqlundercover.com';
GO
Now that we’ve got alerts and an operator, all that’s left is tell SQL to send any notifications for those alerts to our operator. the proc we’re going to be using to do this is sp_add_notification.
The following code will setup all the notifications for your high severity alerts. Make sure you change the operator name to whatever you’ve called your operator.
--setup notifications
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 17 Error:', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 18 Error: Nonfatal Internal Error', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 19 Error: Fatal Error in Resource', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 20 Error: Fatal Error in Current Process', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 21 Error: Fatal Error in Database Process', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 22 Error Fatal Error: Table Integrity Suspect', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 23 Error: Fatal Error Database Integrity Suspect', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 24 Error: Fatal Hardware Error', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
EXEC dbo.sp_add_notification
@alert_name = N'URGENT: Severity 25 Error: Fatal Error', @operator_name = N'SQLUndercoverDBAs', @notification_method = 1 ;
And there you have it, the next time your SQL Server has a grizzle you’ll have an email land in your inbox and you’ll know about it straight away.
SQL Undercover 
Leave a Reply