Disabled replication jobs

This metric investigates replication jobs, searching for any that may be disabled inappropriately. If any of your replication jobs have been disabled inappropriately, you may not be aware of their state.

Install metric...

Metrics install automatically if you have Redgate Monitor installed.

If you are using Redgate’s SQL Server monitoring tool, Redgate Monitor, you can instantly install and run this metric on your servers.

This metric investigates replication jobs, searching for any that may be disabled inappropriately.

If any of your replication jobs have been disabled inappropriately, you may not be aware of their state. If too much time passes, your publication may expire. This will inevitably cause you grief in recreating the publication. Ensuring that your replication jobs are functioning as desired is paramount. Monitoring them is vital to the health of your replication topology.

The associated alert will keep a vigilant eye on your systems, ensuring that the appropriate jobs remain in an enabled state and allowing their functionality to continue executing properly.

To analyze further and see the status of all replication jobs, execute the following query:

[sql]SELECT @@ServerName AS [ServerName],
sj.[Name],
CASE WHEN sj.[enabled] = 1 THEN ‘Enabled’
ELSE ‘Not Enabled – Please Investigate’
END AS [Enabled],
ISNULL(run_date, ”) AS [Run_Date]
FROM msdb.dbo.sysjobs sj
LEFT OUTER JOIN (SELECT MAX(CASE WHEN run_date IS NULL THEN ”
ELSE SUBSTRING(CONVERT(VARCHAR, sh.run_date),
1, 4) + ‘-‘
+ SUBSTRING(CONVERT(VARCHAR, sh.run_date),
5, 2) + ‘-‘
+ SUBSTRING(CONVERT(VARCHAR, sh.run_date),
7, 2)
END) AS [run_date],
job_id
FROM msdb.dbo.sysjobhistory sh
WHERE Step_id = 0
GROUP BY job_id
) AS sh
ON
sh.job_id = sj.job_id
WHERE (sj.name LIKE ‘%Distribution%’
OR sj.name LIKE ‘%LogReader%’)
AND sj.name NOT LIKE ‘%backup%’
AND sj.name NOT LIKE ‘Replication monitoring refresher for distribution.’
ORDER BY [run_date] DESC,
sj.[name];[/sql]

Note: This metric works on SQL Server 2000 SP4 and later versions.

Metric definition

Name

Monitor replication jobs

Description

This metric investigates replication jobs, searching for any that may be disabled inappropriately.

If any of your replication jobs have been disabled inappropriately, you may not be aware of their state. If too much time passes, your publication may expire. This will inevitably cause you grief in recreating the publication. Ensuring that your replication jobs are functioning as desired is paramount. Monitoring them is vital to the health of your replication topology.

The associated alert will keep a vigilant eye on your systems, ensuring that the appropriate jobs remain in an enabled state and allowing their functionality to continue executing properly.

To analyze further and see the status of all replication jobs, execute the following query:

SELECT @@ServerName AS [ServerName],
    sj.[Name],
    CASE WHEN sj.[enabled] = 1 THEN 'Enabled'
         ELSE 'Not Enabled - Please Investigate'
    END AS [Enabled],
    ISNULL(run_date, '') AS [Run_Date]
  FROM msdb.dbo.sysjobs sj 
  LEFT OUTER JOIN (SELECT MAX(CASE WHEN run_date IS NULL THEN ''
                                   ELSE SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                  1, 4) + '-'
                                        + SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                    5, 2) + '-'
                                        + SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                    7, 2)
                              END) AS [run_date],
                      job_id
                    FROM msdb.dbo.sysjobhistory sh
                    WHERE Step_id = 0
                    GROUP BY job_id
                  ) AS sh
  ON
    sh.job_id = sj.job_id
  WHERE (sj.name LIKE '%Distribution%'
    OR sj.name LIKE '%LogReader%')
    AND sj.name NOT LIKE '%backup%'
    AND sj.name NOT LIKE 'Replication monitoring refresher for distribution.'
  ORDER BY [run_date] DESC,
    sj.[name];

The T-SQL query that will collect data

Instances to collect from

Manually select instances containing the databases you’re interested in.

Databases to collect from

MSDB

Collection frequency

3600

Use collected or calculated values

Leave the Use a calculated rate of change between collections check box unchecked

Metric collection

Enabled

Alert definition

Alert name

Disabled replication jobs

Description

This alert is raised when replication jobs that may have been disabled inappropriately are detected. It ensures that the appropriate jobs remain in an enabled state, allowing their functionality to continue executing properly.

If any of your replication jobs have been disabled inappropriately, you may not be aware of their state. If too much time passes, your publication may expire. This will inevitably cause you grief in recreating the publication. Ensuring that your replication jobs are functioning as desired is paramount. Monitoring them is vital to the health of your replication topology.

To analyze further and see the status of all replication jobs, execute the following query:

SELECT @@ServerName AS [ServerName],
    sj.[Name],
    CASE WHEN sj.[enabled] = 1 THEN 'Enabled'
         ELSE 'Not Enabled - Please Investigate'
    END AS [Enabled],
    ISNULL(run_date, '') AS [Run_Date]
  FROM msdb.dbo.sysjobs sj 
  LEFT OUTER JOIN (SELECT MAX(CASE WHEN run_date IS NULL THEN ''
                                   ELSE SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                  1, 4) + '-'
                                        + SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                    5, 2) + '-'
                                        + SUBSTRING(CONVERT(VARCHAR, sh.run_date),
                                                    7, 2)
                              END) AS [run_date],
                      job_id
                    FROM msdb.dbo.sysjobhistory sh
                    WHERE Step_id = 0
                    GROUP BY job_id
                  ) AS sh
  ON
    sh.job_id = sj.job_id
  WHERE (sj.name LIKE '%Distribution%'
    OR sj.name LIKE '%LogReader%')
    AND sj.name NOT LIKE '%backup%'
    AND sj.name NOT LIKE 'Replication monitoring refresher for distribution.'
  ORDER BY [run_date] DESC,
    sj.[name];

Raise an alert when the metric value goes

Above the defined threshholds

Default threshold values

High:0
Medium:Turn off check box
Low:Turn off check box

Note: These thresholds are intended as guideline values. If they seem too high or too low for your environment, replace them with values more suited to your server performance.

Raise an alert when the threshold is passed for

1 collection

Alert is

Enabled