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 – 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