One of the things that I think is extremely important for DBAs and really anyone that has to administer a SQL Server instance is a set of alerts on various items that might occur.
I ran across Glenn Berry’s article on Provisioning SQL Server and there’s a section in there that is titled “Setting Up SQL Server Agent Alerts”. It assumes you have Agent running (and you should), and includes a series of alerts that you can set to inform you when certain things occur.
Glenn has alerts for severity errors 19-25, which are important, and then includes an alert for Error 825. This error is indicative of possible corruption and at least some I/O issue with your storage. It’s important to have this error trapped, but in addition, I’d also recommend setting alerts for 823 and 824 errors.
I would recommend modifying Glenn’s code to include this T-SQL as well.
-- Error 823: Read Write Request Failure DECLARE @Error823AlertName SYSNAME = N'I/O Alert - Error 823: Read or Write request failure'; EXEC msdb.dbo.sp_add_alert @name = @Error823AlertName, @message_id=823, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'; EXEC msdb.dbo.sp_add_notification @alert_name = @Error823AlertName, @operator_name=@OperatorName, @notification_method = 1; GO -- Error 824: Read Write Request Failure DECLARE @Error824AlertName SYSNAME = N'I/O Alert - Error 824: Logical Consistency I/O Error'; EXEC msdb.dbo.sp_add_alert @name = @Error824AlertName, @message_id=824, @Severity=0, @enabled=1, @delay_between_responses=900, @include_event_description_in=1, @category_name=N'[Uncategorized]', @job_id=N'00000000-0000-0000-0000-000000000000'; EXEC msdb.dbo.sp_add_notification @alert_name = @Error824AlertName, @operator_name=@OperatorName, @notification_method = 1; GO
Filed under: Blog Tagged: administration, sql server, syndicated