Enable those alerts!

Since SQL Server 7.0 Microsoft has implemented so called alerts in SQL Agent.
The main purpose of these alerts is that when specific events occur, the DBA automatically gets informed by mail / pager or other means of these event(s) and take corrective action.

There are a couple of basic event that should be implemented on an instance by default but I’ve haven’t seen that many servers were the actually were installed, used and monitored.

Here’s a T-SQL script that will create the right basic alerts that, after setup, will pop-up under the ‘Alert’ folder in SQL Agent. It’s an easy step to connect the alerts to an operator so they can be sent out. I’ll leave this challenge to you ;-)

USE [msdb]
GO
/****** Object:  Alert [019 - Fatal Error in Resource] 13:29:32 ******/
EXEC msdb.dbo.sp_add_alert @name=N'019 - Fatal Error in Resource',
        @message_id=0,
        @severity=19,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [020 - Fatal Error in Current Process] ******/
EXEC msdb.dbo.sp_add_alert @name=N'020 - Fatal Error in Current Process',
        @message_id=0,
        @severity=20,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [021 - Fatal Error in Database Process] ******/
EXEC msdb.dbo.sp_add_alert @name=N'021 - Fatal Error in Database Process',
        @message_id=0,
        @severity=21,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [022 - Fatal Error Table Integritiy Suspect] ******/
EXEC msdb.dbo.sp_add_alert @name=N'022 - Fatal Error Table Integritiy Suspect',
        @message_id=0,
        @severity=22,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [023 - Fatal Error Database Integrity Suspect] ******/
EXEC msdb.dbo.sp_add_alert @name=N'023 - Fatal Error Database Integrity Suspect',
        @message_id=0,
        @severity=23,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'

USE [msdb]
GO
/****** Object:  Alert [024 - Fatal Error Hardware Error]  ******/
EXEC msdb.dbo.sp_add_alert @name=N'024 - Fatal Error Hardware Error',
        @message_id=0,
        @severity=24,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'


USE [msdb]
GO
/****** Object:  Alert [025 - Fatal Error] ******/
EXEC msdb.dbo.sp_add_alert @name=N'025 - Fatal Error',
        @message_id=0,
        @severity=25,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1,
        @category_name=N'[Uncategorized]',
        @job_id=N'00000000-0000-0000-0000-000000000000'