SQL Agent Alerts

I’m going to step away from my Troubleshootingwith DMO’s series briefly to write a piece on SQL Agent Alerts. These alerts are widely considered best practice to set up as part of your post install process. I’m in complete agreement with that (and they do form part of my trusty instance setup script) but actually quite often I encounter instances without any of these alerts configured.

These alerts cover a range of errors from potential IO subsystem problems to failed logins, all of which are things a DBA needs to know about, and quickly too.

As well as error notifications you can set up alerts to cover performance conditions. The final statement in the script below sets up an alert that triggers when Page Life Expectancy drops below 1000. In all honesty I don’t set up these performance alerts that often but I wanted to show you the kind of thing that is possible and would be handy if you don’t have any third party monitoring.

So the following script will setup a variety of alerts on your instance. What they WON’T do at this time is provide a response or notification. I wanted to go into that in a little more detail so there's quite a comprehensive post on that topic which you can read here.


USE [msdb]

GO

--SQL Agent Alerts

/*

Errors 823-825 indicate a potential IO subsystem error and must be followed
with a instance level consistency check.

*/

EXEC msdb.dbo.sp_add_alert @name=N'Error 823 - Error on Read/Write Request',

              @message_id=823,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 824 - Error on SQL Server Page Read',

              @message_id=824,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Error 825 - I/O Error Retry Success',

              @message_id=825,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

/*

Severity 014, 016-024
*/

EXEC msdb.dbo.sp_add_alert @name=N'Severity 014 - Failed Logins or Insufficient Permission',

              @message_id=0,

              @severity=14,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 016 - Misc User Error',

              @message_id=0,

              @severity=16,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 017 - Insufficient Resource',

              @message_id=0,

              @severity=17,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 018 - Nonfatal Internal Error',

              @message_id=0,

              @severity=18,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 019 - Fatal Error in Resource',

              @message_id=0,

              @severity=19,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 020 - Fatal Error in Current Process',

              @message_id=0,

              @severity=20,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO

EXEC msdb.dbo.sp_add_alert @name=N'Severity 021 - Fatal Error in Database Process',

              @message_id=0,

              @severity=21,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'

GO


EXEC msdb.dbo.sp_add_alert @name=N'Severity 022 - Fatal Error: Table Integrity Suspect',

              @message_id=0,

              @severity=22,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO


EXEC msdb.dbo.sp_add_alert @name=N'Severity 023 - Fatal Error: Database Integrity Suspect',

              @message_id=0,

              @severity=23,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'
GO


EXEC msdb.dbo.sp_add_alert @name=N'Severity 024 - Fatal Error: Hardware Error',

              @message_id=0,

              @severity=24,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @job_id=N'00000000-0000-0000-0000-000000000000'

GO

/*

Example performance related alert
*/

EXEC msdb.dbo.sp_add_alert @name=N'Performance Alert - Page Life Expectancy',

              @message_id=0,

              @severity=0,

              @enabled=1,

              @delay_between_responses=0,

              @include_event_description_in=0,

              @category_name=N'[Uncategorized]',

              @performance_condition=N'Buffer Manager|Page life expectancy||<|1000',

              @job_id=N'00000000-0000-0000-0000-000000000000'

GO