Skip to content

Best Practice Alerts & Notifications with SQL Script

by on April 24, 2015

I got tired of doing this by hand, and had a moment so I scripted out the SQL Alerts And Notifications

tSQL Query

USE msdb ;
GO
----------------LOW
EXEC dbo.sp_add_alert
   @name = N'Severity Level 014: Insufficient Permissions - Low',
   --@message_id = 55001, 
   @severity = 14, 
   @notification_message = N'Description: Non-serious errors. Often is considered user correctable.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
---------------MEDIUM
EXEC dbo.sp_add_alert
   @name = N'Severity Level 017: Insufficient Resources - Medium',
   --@message_id = 55001, 
   @severity = 17, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 018: Nonfatal Internal Error - Medium',
   --@message_id = 55001, 
   @severity = 18, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 019: Error In Resources - Medium',
   --@message_id = 55001, 
   @severity = 19, 
   @notification_message = N'Description: Severity levels from 17 through 19 will require 
   intervention from a DBA, they are not as serious as 20-25 but the DBA needs to be alerted.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
------------HIGH
EXEC dbo.sp_add_alert
   @name = N'Severity Level 020: Error In Current Process - High',
   --@message_id = 55001, 
   @severity = 20, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 021: Fatal Error In DB DBID Processes - High',
   --@message_id = 55001, 
   @severity = 21, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 022: Fatal Error Table Integrity Suspect - High',
   --@message_id = 55001, 
   @severity = 22, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 023: Fatal Error DB Integrity Suspect - High',
   --@message_id = 55001, 
   @severity = 23, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 024: Fatal Error Hardware – High',
   --@message_id = 55001, 
   @severity = 24, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
EXEC dbo.sp_add_alert
   @name = N'Severity Level 025: Fatal Error Hardware – High',
   --@message_id = 55001, 
   @severity = 25, 
   @notification_message = N'Description: Severity Levels 20 through 25 are serious errors 
   that mean SQL Server is no longer working, notify the DBA immediately.', 
   @delay_between_responses =5,
   @include_event_description_in = 1
   --,@job_name = N'Back up the AdventureWorks2012 Database' ;
GO
-------------Create Operator
EXEC dbo.sp_add_operator
    @name = N'Ebruce',
    @enabled = 1,
    @email_address = N'ebruce@mydomain.com'
    --@pager_address = N'5551290AW@pager.Adventure-Works.com',
    --@weekday_pager_start_time = 080000,
    --@weekday_pager_end_time = 170000,
    --@pager_days = 62 ;
GO
------------Assign Alerts to Operator
EXEC dbo.sp_add_notification N'Severity Level 014: Insufficient Permissions - Low', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 017: Insufficient Resources - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 018: Nonfatal Internal Error - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 019: Error In Resources - Medium', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 020: Error In Current Process - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 021: Fatal Error In DB DBID Processes - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 022: Fatal Error Table Integrity Suspect - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 023: Fatal Error DB Integrity Suspect - High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 024: Fatal Error Hardware – High', N'Ebruce', 1 ;
EXEC dbo.sp_add_notification N'Severity Level 025: Fatal Error Hardware – High', N'Ebruce', 1 ;
Advertisements

From → Microsoft, SQL Server

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: