See Also: Main_Page - Transact SQL Code Library

The following Error Management Functions help the database administrator stay on top of errors that happen on many servers. The Operator and Alerts setup scripts should be run on every database server in the company so that alerts are sent as soon as they happen on any server, anywhere. This helps the DBA to react faster when trouble strikes.

Set Up Database Mail

On SQL Server 2005 and newer, Database Mail is a replacement for SQLmail. The older SQLmail (in SQL Server 2000) required Outlook to be set up on the database server itself. Database Mail is much more robust and reliable.

We don't have scripts here yet to set up Database Mail, but we're mentioning it because you need Database Mail enabled before using Operators and Alerts.

Set Up Operators

The below script will set up an operator. You'll need to replace 3 lines in the code:

• Replace 'Administrator' with your name, like 'John Doe'
• Replace 'myemailaddress@mydomainname.com' with your email address, like johndoe@contoso.com
• Replace 'mypageremailaddress@mypagerdomainname.com' with your pager's email address. For a list of SMS pager gateways, see: http://en.wikipedia.org/wiki/SMS_gateway

USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'Administrator', 
		@enabled=1, 
		@weekday_pager_start_time=0, 
		@weekday_pager_end_time=235959, 
		@saturday_pager_start_time=0, 
		@saturday_pager_end_time=235959, 
		@sunday_pager_start_time=0, 
		@sunday_pager_end_time=235959, 
		@pager_days=127, 
		@email_address=N'myemailaddress@mydomainname.com', 
		@pager_address=N'mypageremailaddress@mypagerdomainname.com', 
		@category_name=N'[Uncategorized]'
GO

Set Up Alerts

After setting up an operator, use the below script to set up email alerts on severity levels 16-25. Replace 'Administrator' with the operator name that was set up - it must match exactly.

The @delay_between_responses=60 parameter means that SQL Server will only send this email alert once per minute even if the alarm is fired many times during that minute. If you only want to get alerts once every 10 minutes, you could change that value to 600 instead of 60.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 016', 
		@message_id=0, 
		@severity=16, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 016', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 017', 
		@message_id=0, 
		@severity=17, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 017', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 018', 
		@message_id=0, 
		@severity=18, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 018', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 019', 
		@message_id=0, 
		@severity=19, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 019', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 020', 
		@message_id=0, 
		@severity=20, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 020', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 021', 
		@message_id=0, 
		@severity=21, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 021', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 022', 
		@message_id=0, 
		@severity=22, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 022', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 023', 
		@message_id=0, 
		@severity=23, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 023', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 024', 
		@message_id=0, 
		@severity=24, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 024', @operator_name=N'Administrator', @notification_method = 7
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity 025', 
		@message_id=0, 
		@severity=25, 
		@enabled=1, 
		@delay_between_responses=60, 
		@include_event_description_in=1, 
		@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity 025', @operator_name=N'Administrator', @notification_method = 7
GO

Get message text by specifying the error number

CREATE PROC error_description @error_number INT  AS  
IF NOT EXISTS (SELECT error FROM master.dbo.sysmessages WHERE error = @error_number)  
BEGIN       
RAISERROR ('there is no error with the number provided, check your records', 16, 1)       
RETURN  
END
SELECT [description] FROM master.dbo.sysmessages WHERE error = @error_number
IF @@ERROR <> 0  BEGIN       
RAISERROR ('error occured while examining sysmessages table', 16, 1) WITH LOG  
RETURN  
END
GO