Here is Brent Ozar’s script to add important alerts: via Agent Alerts Not Configured – Brent Ozar Unlimited®
The below script sets up SQL Server Agent alerts for severity 16 through 25 as well as specific alerts for 823, 824 and 825 errors. Do a search & replace in this code and change ‘The DBA Team’ to the name of your SQL Agent operator – and remember, always use email distribution lists for your operator emails, not individual peoples’ emails. Someday – not soon, but someday – you’re going to go on vacation, and you want other people to get notified when you’re gone.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 016′,
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 016′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 017′,
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 017′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 018′,
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 018′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 019′,
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 019′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 020′,
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 020′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 021′,
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 021′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 022′,
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 022′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 023′,
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 023′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 024′,
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 024′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Severity 025′,
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′;
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Severity 025′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Error Number 823′,
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Error Number 823′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Error Number 824′,
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Error Number 824′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
EXEC msdb.dbo.sp_add_alert @name=N’Error Number 825′,
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=60,
@include_event_description_in=1,
@job_id=N’00000000-0000-0000-0000-000000000000′
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N’Error Number 825′, @operator_name=N’The DBA Team’, @notification_method = 7;
GO
