T-SQL: the primordial Alerts (Best Practices)
-- First we need to create an operator that will receive notifications.
Use msdb
Go
If Not Exists(Select name From msdb.dbo.sysoperators
Where name = N’DBAdministrator’)
Exec msdb.dbo.sp_add_operator @name=N’DBAdministrator’,
@enabled=1,
@pager_days=0,
@email_address=N'account@mailserver.com'
Go
-- Alert1 : insufficient resources.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Insufficient resources')
Exec msdb.dbo.sp_add_alert @name= N'Insufficient resources',
@message_id=0,
@severity=17,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Insufficient resources'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Insufficient resources',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert2 : recoverable internal error.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Recoverable internal error ')
Exec msdb.dbo.sp_add_alert @name= N'Recoverable internal error ',
@message_id=0,
@severity=18,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Recoverable internal error '
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Recoverable internal error ',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert3 : fatal error resource.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Fatal error resource ')
Exec msdb.dbo.sp_add_alert @name= N'Fatal error resource ',
@message_id=0,
@severity=19,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Fatal error resource '
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error resource ',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert4 : fatal error during processing.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Fatal error during processing')
Exec msdb.dbo.sp_add_alert @name= N'Fatal error during processing',
@message_id=0,
@severity=20,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Fatal error during processing'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error during processing',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert5 : fatal error in the treatment database.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N' Fatal error in the treatment database’)
Exec msdb.dbo.sp_add_alert @name= N' Fatal error in the treatment database’,
@message_id=0,
@severity=21,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N' Fatal error in the treatment database’
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N' Fatal error in the treatment database’,
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert6 : fatal error (questionable integrity of the table).
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Fatal error (questionable integrity of the table)')
Exec msdb.dbo.sp_add_alert @name= N'Fatal error (questionable integrity of the table)',
@message_id=0,
@severity=22,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Fatal error (questionable integrity of the table)'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error (questionable integrity of the table)',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert7 : fatal error : dubious integrity of the database.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N' Fatal error : dubious integrity of the database')
Exec msdb.dbo.sp_add_alert @name= N' Fatal error : dubious integrity of the database',
@message_id=0,
@severity=23,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N' Fatal error : dubious integrity of the database'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N' Fatal error : dubious integrity of the database',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert8 : fatal error (hardware error).
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Fatal error (hardware error)')
Exec msdb.dbo.sp_add_alert @name= N'Fatal error (hardware error)',
@message_id=0,
@severity=24,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Fatal error (hardware error)'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error (hardware error)',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert9 : fatal error.
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Fatal error')
Exec msdb.dbo.sp_add_alert @name= N'Fatal error',
@message_id=0,
@severity=25,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Fatal error'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Fatal error',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert10 : error number 823 (hardware or system problem)
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N'Error Number 823 (hardware or system problem)')
Exec msdb.dbo.sp_add_alert @name= N'Error Number 823 (hardware or system problem)',
@message_id=823,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message= N'Error Number 823 (hardware or system problem)',
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N'Error Number 823 (hardware or system problem)'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N'Error Number 823 (hardware or system problem)',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert11 : error number 824 (I/O logic error).
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N' Error Number 824 (I/O logic error)')
Exec msdb.dbo.sp_add_alert @name= N' Error Number 824 (I/O logic error)',
@message_id=824,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message=N'Erreur logique I/O',
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N' Error Number 824 (I/O logic error)'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N' Error Number 824 (I/O logic error)',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go
-- Alert12 : error number 825 (rerun reading - major error with the disk drive).
If Not Exists(Select name From msdb.dbo.sysalerts
Where name = N' Error Number 825 (rerun reading - major error with the disk drive)')
Exec msdb.dbo.sp_add_alert @name= N' Error Number 825 (rerun reading - major error with the disk drive)',
@message_id=825,
@severity=0,
@enabled=1,
@delay_between_responses=0,
@include_event_description_in=1,
@notification_message= N' Error Number 825 (rerun reading - major error with the disk drive)',
@job_id=N'00000000-0000-0000-0000-000000000000'
Go
-- Notify the operator.
If Not Exists(Select notifications.notification_method, alerts.name, operators.name
From msdb.dbo.sysnotifications as notifications
inner join msdb.dbo.sysalerts as alerts on notifications.alert_id = alerts.id
inner join msdb.dbo.sysoperators as operators on notifications.operator_id = operators.id
Where alerts.name = N' Error Number 825 (rerun reading - major error with the disk drive)'
and operators.name = N’DBAdministrator’
and notification_method = 1)
Exec msdb.dbo.sp_add_notification @alert_name= N' Error Number 825 (rerun reading - major error with the disk drive)'',
@operator_name=N’DBAdministrator’, @notification_method = 1
Go