Archive for the ‘Monitoring’ Category

How to create SQL Server Severity Alerts?

In hospitals or clinics, there are pain charts for the doctors or nurses to ask the patients the level of pain. Similarly, when an error is raised by SQL Server, there are levels of severity associated with error to indicate the type of problem encountered by SQL Server.

AS per Microsoft, severity levels 0 to 9 are informational messages or errors that are not severe. Severity levels 11 to 16 indicate errors that can be corrected by the user. Severity levels 17 to 19 are errors that cannot be correct by the user and should inform the SQL Server DBAs. Severity levels 20 to 24 are fatal system errors that are written to the error log and should be escalated to the attention of the SQL Server DBAs. 823, 824, and 825 are I/O errors. 823 is associated with disk corruption. 824 indicates logical consistency I/O errors. 825 indicates incorrect checksum errors.

Prior to creating these SQL Server severity alerts, you should have first configured database mail and an operator. To create the severity alerts, you can use the following script:

-- Creating Severity alerts from 17-25
-- The operator should be created prior to creating the severity alerts.
-- Replace OperatorName with the name of your operator.
-- Error messages with a severity level from 19 through 25 are written to the error log.

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 17 Alert: Insufficient Resources',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 17 Alert: Insufficient Resources',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 18 Alert: Nonfatal Internal Error',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 18 Alert: Nonfatal Internal Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 19 Alert: Fatal Error in Resource',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 19 Alert: Fatal Error in Resource',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 20 Alert: 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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 20 Alert: Fatal Error in Current Process',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 21 Alert: Fatal Error in Database Processes',
  @message_id=0,
  @severity=21,
  @enabled=1,
  @delay_between_responses=0,
  @include_event_description_in=1,
  @job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 21 Alert: Fatal Error in Database Processes',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 22 Alert: Fatal Error - Table or Index Integrity Suspect',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 22 Alert: Fatal Error - Table or Index Integrity Suspect',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 23 Alert: Fatal Error - Database Integrity Suspect',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 23 Alert: Fatal Error - Database Integrity Suspect',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 24 Alert: Fatal Error - Hardware Error',
  @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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 24 Alert: Fatal Error - Hardware Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 25 Alert: Fatal Error - Hardware 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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 25 Alert: Fatal Error - Hardware Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 823 Alert: Fatal Error - I/O 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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 823 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 824 Alert: Fatal Error - I/O 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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 824 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N'Severity Level 825 Alert: Fatal Error - I/O 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
EXEC msdb.dbo.sp_add_notification @alert_name=N'Severity Level 825 Alert: Fatal Error - I/O Error',
@operator_name=N'OperatorName',
@notification_method = 1
GO

REFERENCES:

Database Engine Error Severities
http://msdn.microsoft.com/en-us/library/ms164086(v=sql.105).aspx

Error message 823
http://support.microsoft.com/kb/828339

Error message 824
http://support.microsoft.com/kb/2015756

Error message 825
http://support.microsoft.com/kb/2015757