SQL Server alerts – early warning support

In SQL Server you can configure Alerts to catch exceptions of different severity levels. It’s a nice feature, but it is not used that often. I have seen a lot of SQL server installations, but few of them have Alerts configured. To make the most of the Alerts, you have to add the option to include the exception text. Otherwise you’ll get an alert, but not what really happend.

My recommendation is to create alerts for severity 17 to 25. All exceptions of 17 and higher are very important to catch, because the are system critical. You can configure them by scripts or by GUI. You can use the script below to add alerts of severity 17 to 25:

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'17 Insufficient Resources Alert', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'18 Nonfatal Internal Error Alert', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'19 Fatal Error in resource Alert', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'20 Fatal Error in current process', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO

/****** Object:  Alert [21 Fatal Error in Database process]    Script Date: 2013-09-15 22:18:20 ******/
EXEC msdb.dbo.sp_add_alert @name=N'21 Fatal Error in Database process', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@category_name=N'[Uncategorized]', 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'22 Fatal Error Table Integrity Alert', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'23 Fatal Error Database Integrity Alert', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'24 Fatal Error Hardware Error Alert', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'25 Fatal Error', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=0, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO



When you have created the alerts, you should assign an operator for them. 

/Håkan Winther

Twitter: @h_winther