Disabled backup jobs

| 2 Comments | 4,848 views
Categories:

VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)

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.

Disabled backup jobs, 5.0 out of 5 based on 1 rating

Metric definition

Metric 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.

Enter the T-SQL query that will collect data:
SELECT COUNT(*) AS [Count]
  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')
    AND sj.[enabled] <> 1;

Note: This query looks for jobs with ‘backup’ in the name. It also excludes specific jobs that are acceptable to be disabled. Replace the following terms with appropriate values for your system and needs, ensuring that they match your naming convention:

[%backup%]

['Daily Backups.Subplan_1']

Select instances to collect from:
Manually select instances containing the databases you’re interested in
Note: Turn on the relevant check boxes.
Choose databases to collect from:
Specify databases and type
MSDB
Set collection frequency:
Collect data every day
Use collected or calculated values:
Leave the Use a calculated rate of change between collections check box turned off
Metric collection is:
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 thresholds
Default threshold values:
Set the thresholds as follows:
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:
Enter 1 collection
This alert is:
Enabled
VN:F [1.9.17_1161]
Rating: 5.0/5 (1 vote cast)
  • Chris

    Will this detect SQL Agent being disabled on the machine?

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)
  • SQL Monitor Team

    Thanks for your question, Chris.

    This metric won’t detect if the SQL Agent is disabled, but SQL Monitor already has an alert called SQL Server Agent Service status which is raised when the agent is stopped or paused.

    VA:F [1.9.17_1161]
    Rating: 0 (from 0 votes)