SQL-DBA-SCRIPTS/035.add_mirror_alerts.sql at master · himadanreddy/SQL-DBA-SCRIPTS · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
USE [msdb]

DECLARE @Enabled            TINYINT
DECLARE @NotificationMethod TINYINT
DECLARE @WMI_NameSpace      NVARCHAR(256)
DECLARE @Operator           NVARCHAR(128);

SELECT @Operator = ParmValue
FROM [DBAdmin].[dbo].[DBAdmin_InstallParms]
WHERE ParmName = 'JobPageOperator';

SELECT @Enabled = CAST(ParmValue AS TINYINT)
    FROM [DBAdmin].[dbo].[DBAdmin_InstallParms]
    WHERE ParmName = 'AlertsEnabled'

IF EXISTS (SELECT *
               FROM [DBAdmin].[dbo].[DBAdmin_InstallParms]
               WHERE ParmName = 'IsProduction'
                AND  ParmValue = '1')
    SELECT @NotificationMethod = 2  -- Pager
ELSE
    SELECT @NotificationMethod = 1  -- Email


IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'DBAdmin Alerts' AND category_class=2)
    BEGIN
        EXEC sp_add_category @class=N'ALERT', @type=N'NONE', @name=N'DBAdmin Alerts'
    END


/****** Object:  Alert [DB Mirroring: Oldest Unsent Transaction Warning]    Script Date: 06/14/2010 14:21:28 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBAdmin: DB Mirroring: Oldest Unsent Transaction Warning',
        @message_id=32040,
        @severity=0,
        @enabled=0,
        @delay_between_responses=600,
        @include_event_description_in=3,
        @notification_message=N'The oldest unsent transaction for a database on this server has exceeded the age specified in the Mirroring Monitor Threshold.',
        @category_name=N'DBAdmin Alerts',
        @job_id=N'00000000-0000-0000-0000-000000000000'
EXEC sp_add_notification @alert_name=N'DBAdmin: DB Mirroring: Oldest Unsent Transaction Warning', @operator_name = @Operator, @notification_method=@NotificationMethod


/****** Object:  Alert [DB Mirroring: Synchronous Mirroring Latency Warning]    Script Date: 06/14/2010 14:21:48 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBAdmin: DB Mirroring: Synchronous Mirroring Latency Warning',
        @message_id=32044,
        @severity=0,
        @enabled=0,
        @delay_between_responses=600,
        @include_event_description_in=3,
        @notification_message=N'The Mirroring Latency for a database on this server has exceeded the value specified in the Mirroring Monitor Threshold.',
        @category_name=N'DBAdmin Alerts',
        @job_id=N'00000000-0000-0000-0000-000000000000'
EXEC sp_add_notification @alert_name=N'DBAdmin: DB Mirroring: Synchronous Mirroring Latency Warning', @operator_name = @Operator, @notification_method=@NotificationMethod


/****** Object:  Alert [DB Mirroring: Unrecovered Log Warning]    Script Date: 06/14/2010 14:22:04 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBAdmin: DB Mirroring: Unrecovered Log Warning',
        @message_id=32043,
        @severity=0,
        @enabled=0,
        @delay_between_responses=600,
        @include_event_description_in=3,
        @notification_message=N'The Un-Recovered log for a database on the Mirror has exceeded the value specified in the Mirroring Monitor Threshold.',
        @category_name=N'DBAdmin Alerts',
        @job_id=N'00000000-0000-0000-0000-000000000000'
EXEC sp_add_notification @alert_name=N'DBAdmin: DB Mirroring: Unrecovered Log Warning', @operator_name = @Operator, @notification_method=@NotificationMethod


/****** Object:  Alert [DB Mirroring: Unsent Log Warning]    Script Date: 06/14/2010 14:22:11 ******/
EXEC msdb.dbo.sp_add_alert @name=N'DBAdmin: DB Mirroring: Unsent Log Warning',
        @message_id=32042,
        @severity=0,
        @enabled=0,
        @delay_between_responses=600,
        @include_event_description_in=3,
        @notification_message=N'The Un-Sent log for a database on the Mirror has exceeded the value specified in the Mirroring Monitor Threshold.',
        @category_name=N'DBAdmin Alerts',
        @job_id=N'00000000-0000-0000-0000-000000000000'
EXEC sp_add_notification @alert_name=N'DBAdmin: DB Mirroring: Unsent Log Warning', @operator_name = @Operator, @notification_method=@NotificationMethod