10,002 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 backup jobs, searching for any that may be disabled inappropriately.
If any of your backup jobs have been disabled inappropriately, you may not be aware of their state. This may cause you grief if backups do not occur properly. Ensuring that your backup jobs are functioning as desired is paramount. Monitoring them is vital to the health of your topology.
The alert associated with this metric will keep a vigilant eye on your systems, ensuring that the appropriate jobs remain in an enabled state, allowing their functionality to continue executing properly.
Note: This query looks for jobs with ‘backup’ in the name. It also excludes specific jobs that are acceptable to be disabled. Enter your own values here as you see fit.
Note: This metric works on SQL Server 2000 SP4 and later versions.
Metric definition
Name
Disabled backup jobs
Description
This metric investigates backup jobs, searching for any that may be disabled inappropriately.
If any of your backup jobs have been disabled inappropriately, you may not be aware of their state. This may cause you grief if backups do not occur properly. Ensuring that your backup jobs are functioning as desired is paramount. Monitoring them is vital to the health of your topology.
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
86400
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 backup jobs
Description
This alert is raised when named backup jobs are in a disabled state. It keeps a vigilant eye on your systems, ensuring that the appropriate jobs remain in an enabled state, and allowing their functionality to continue executing properly.
Execute the following query to see status of all backup jobs:
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 '%backup%'
AND sj.name NOT IN ('Daily Backups.Subplan_1')
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
