The following alerts are typically setup at a minimum to keep an eye on your SQL Server.
USE [msdb]
GO
EXEC msdb.dbo.sp_add_operator @name=N'sql_alerts',
@enabled=1,
@weekday_pager_start_time=90000,
@weekday_pager_end_time=180000,
@saturday_pager_start_time=90000,
@saturday_pager_end_time=180000,
@sunday_pager_start_time=90000,
@sunday_pager_end_time=180000,
@pager_days=0,
@email_address=N'destinationemailaddress1@yourcompany.com;destinationemailaddress2@yourcompany.com',
@category_name=N'[Uncategorized]'
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_14',
@message_id=0,
@severity=14,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_14', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_15',
@message_id=0,
@severity=15,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_15', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_16',
@message_id=0,
@severity=16,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_16', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_17',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_17', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_18',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_18', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_19',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_19', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_20',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_20', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_21',
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_21', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_22',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_22', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_23',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_23', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_24',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_24', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Sev_25',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Sev_25', @operator_name=N'sql_alerts', @notification_method = 1
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_823',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_823', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_824',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_824', @operator_name=N'sql_alerts', @notification_method = 1;
GO
EXEC msdb.dbo.sp_add_alert @name=N'Error_825',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=300,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
GO
EXEC msdb.dbo.sp_add_notification @alert_name=N'Error_825', @operator_name=N'sql_alerts', @notification_method = 1;
GO