Monitoring through SQL Server Event Alerts

Maintaining 99.9 % Availability of database servers is one of the main goals of a DBA.  To achieve this goal, an enterprise monitoring tool is required. There are various third party monitoring tools available in the market. Such as

1. Red-gate SQL Monitor

2. Idera – SQL Diagnostic Manager

3. Microsoft- SCOM

4. BMC etc.

However, all these Monitoring tools are very expensive.  And most of the organizations can’t afford them. So, configuring SQL Server Event Alerts in SQL Agent is a free and easiest way to get notified of corruption, job failures, or major outages even before monitoring systems pick it up.

The script configures SQL Server Alerts for severity 16 to 25.

Severity:

1

 

 

 

— Note: You need to configure Database Mail and Operator before run this script. In this script i used the operator name DB Team. You need to change the operator name accordingly.

Script:

EventAlertsScript____________________________________________________________________________________________________

–Script to configure SQL Server Event Alerts in SQL Agent

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’DB 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’DB 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’DB 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’DB 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’DB 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’DB 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’DB 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’DB 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’DB 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’DB Team’, @notification_method = 7
GO

_________________________________________________________________________________________________________________________________

This is how basic level of notification alerts you can configure for free.

 

 

 

Advertisements