http://www.sqlservercentral.com/blogs/steve_jones/2012/10/18/important-alerts-for-sql-server/

Printed 2014/07/30 03:18PM

Important Alerts for SQL Server

By Steve Jones, 2012/10/18

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
Copyright © 2002-2014 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.