Wednesday 11 January 2012

System Stored Procedures

I've been using 4 alerts for corruption now for about 2 years for Errors 823, 824, 825 and 9100.  Basically I would create an operator via SSMS, the four alerts via T-SQL then manually add the operator to the response through SSMS.  Now given that I only did this during the initial server setup I guess I was just lazy and didn't script the whole process.

So I thought being in a new job and wanting to get these alerts set-up across the estate I would create a script to do just that.  In doing so I couldn't remember the table containing the operators was it sys. or dbo. or operators (IntelliSense was also playing up) at this point I gave up and got the text of the system stored procedure msdb.dbo.sp_add_operator from which I found that the table was msdb.dbo.sysoperators.  

This is something I do quite a lot, there are some very knowledgeable people at Microsoft who spend a lot of time writing some fantastic system procedures.  These are worth a look, this time I only learned a table name I was looking for but who knows what I will next time.  The next time you get a few minutes have a nosey you just never know what you will find.

Below is the script I created, the Operator @name, @email_address and Notification @operator_name variables need updating accordingly along with the IF NOT EXISTS check but that is it.  

NOTE - This script is provided as is and has been tested on SQL Server 2005 and SQL Server 2008.  I always recommend running scripts on a non production environment first.

USE [msdb] ;
GO
-- Create the Operator
IF NOT EXISTS ( SELECT  1
                FROM    msdb.dbo.sysoperators
                WHERE   name = '** Operator Here ***' )
    BEGIN
        EXEC msdb.dbo.sp_add_operator @name = N'*** Operator Here ***',
            @enabled = 1, @email_address = N'*** Email Address Here ***'
    END ;
GO

-- Alert 823 - Hard I/O Corruption
IF EXISTS ( SELECT  name
            FROM    msdb.dbo.sysalerts
            WHERE   name = N'823 - Hard I/O Corruption' )
    EXEC msdb.dbo.sp_delete_alert @name = N'823 - Hard I/O Corruption' ;
GO
EXEC msdb.dbo.sp_add_alert @name = N'823 - Hard I/O Corruption',
    @message_id = 823, @severity = 0, @enabled = 1,
    @delay_between_responses = 0, @include_event_description_in = 5,
    @notification_message = N'This is where SQL Server has asked the OS to read the page but it just cant',
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000' ;
GO
-- Add Notification
EXEC msdb.dbo.sp_add_notification @alert_name = N'823 - Hard I/O Corruption',
    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;
GO

-- Alert [824 - Soft I/O Corruption]
IF EXISTS ( SELECT  name
            FROM    msdb.dbo.sysalerts
            WHERE   name = N'824 - Soft I/O Corruption' )
    EXEC msdb.dbo.sp_delete_alert @name = N'824 - Soft I/O Corruption' ;
GO
EXEC msdb.dbo.sp_add_alert @name = N'824 - Soft I/O Corruption',
    @message_id = 824, @severity = 0, @enabled = 1,
    @delay_between_responses = 0, @include_event_description_in = 5,
    @notification_message = N'This is where the OS could read the page but SQL Server decided that the page was corrupt - for example with a page checksum failure',
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000' ;
GO
-- Add Notification
EXEC msdb.dbo.sp_add_notification @alert_name = N'824 - Soft I/O Corruption',
    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;
GO

-- Alert Corruption: Read/Retry 825
IF EXISTS ( SELECT  name
            FROM    msdb.dbo.sysalerts
            WHERE   name = N'Corruption: Read/Retry 825' )
    EXEC msdb.dbo.sp_delete_alert @name = N'Corruption: Read/Retry 825' ;
GO
EXEC msdb.dbo.sp_add_alert @name = N'Corruption: Read/Retry 825',
    @message_id = 825, @severity = 0, @enabled = 1,
    @delay_between_responses = 600, @include_event_description_in = 5,
    @notification_message = N'This is where either an 823 or 824 occured, SQL server retried the IO automatically and it succeeded. This error is written to the errorlog only - you need to be aware of these as they''re a sign of your IO subsystem going awry. There''s no way to turn off read-retry and force SQL Server to ''fail-fast'' - whether this behavior is a good or bad thing can be argued both ways - personally I don''t like it',
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000' ;
GO
-- Add Notification
EXEC msdb.dbo.sp_add_notification @alert_name = N'Corruption: Read/Retry 825',
    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;
GO

-- Alert Error - 9100 (Index Corruption)
IF EXISTS ( SELECT  name
            FROM    msdb.dbo.sysalerts
            WHERE   name = N'Error - 9100 (Index Corruption)' )
    EXEC msdb.dbo.sp_delete_alert @name = N'Error - 9100 (Index Corruption)' ;
GO
EXEC msdb.dbo.sp_add_alert @name = N'Error - 9100 (Index Corruption)',
    @message_id = 9100, @severity = 0, @enabled = 1,
    @delay_between_responses = 180, @include_event_description_in = 7,
    @category_name = N'[Uncategorized]',
    @job_id = N'00000000-0000-0000-0000-000000000000' ;
GO
-- Add Notification
EXEC msdb.dbo.sp_add_notification @alert_name = N'Error - 9100 (Index Corruption)',
    @operator_name = N'*** Operator Here ***', @notification_method = 1 ;
GO

Enjoy! 

Chris

3 comments:

  1. Thanks Chris, I've been looking for more alerts to stay on top of SQL Server besides the standard 17-25

    ReplyDelete
  2. Glad you found it useful Doug.

    ReplyDelete