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