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
