Agent Jobs Check (MS2000)


Job details

Name: Agent Jobs Check (MS2000)
Platform: Sqlserver
Category: Availability
Description: Checks whether there exists jobs on the SQL server which have not been executed or have failed during execution.
Long description:
Version: 1.4
Default schedule: 0,30 * * *
Requires engine install: Yes
Compatibility tag: .[type=‘instance’ & databasetype=‘sqlserver’]/instance[maj_version = ‘2000′ & hasengine=‘YES’ & engine_edition = ‘Microsoft SQL Server’]

Parameters

Name Default value Description
ignore jobs A comma separated list of jobs to ignore

Job Summary

SELECT job.name, jobhistory.message, jobhistory.run_status FROM msdb.dbo.sysjobs AS job INNER JOIN msdb.dbo.sysjobhistory AS jobhistory ON job.job_id = jobhistory.job_id WHERE jobhistory.run_status = 0; 

Job Description

Checks for SQL Server Agent jobs that have either failed to execute according to their schedule or have encountered errors during their execution. Specifically, it:

Dependencies and Resources

The job leverages multiple SQL Server tables and a custom set of stored procedures and functions to retrieve, compile, and report on the job statuses. The primary SQL Server objects involved are:

Object Type Description
msdb.dbo.sysjobhistory Table Stores the history of job executions, including status and error messages.
msdb.dbo.sysjobs Table Contains job definitions and their current enabled/disabled status.
msdb.dbo.sysjobschedules Table Relates jobs to their schedules, providing the expected execution times.
dbw_agent_Jobs_Check Stored Procedure Compiles job execution details and reports failures.
dbw_convertStringToDate Function Converts combined date and time integer values into a datetime format for easier assessment.
dbw_statusName Helper Table Maps integer status codes from job history to human-readable status messages.
dbw_ignoreJobs Helper Table Used to store jobs that should be ignored during the check.
dbw_lastCheckExecDate Helper Table Tracks the last execution date of the job check to filter out older records.

Implementation Code

The core of the implementation is a SQL stored procedure that:

This combination of procedures, tables, and checks forms a robust monitoring mechanism tailored to maintaining the reliability and timely operation of SQL Server Agent jobs.