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

Purpose: The purpose of this job is to monitor and check the status of SQL Server Agent jobs, particularly focusing on jobs that have failed or not executed as expected on a SQL Server 2000 database instance.
Why: This monitoring is crucial for ensuring the smooth operation of scheduled tasks within the SQL Server environment. Identifying and resolving issues with agent jobs can prevent potential failures and ensure that necessary database maintenance and business processes are executed on time.
Manual checking: You can manually check the status of SQL Server Agent jobs by executing the following SQL command:

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:

Identifies and lists all the jobs that have failed since the last execution of the check.
Distinguishes jobs that are scheduled but have not been run yet due to various reasons, such as being newly created or disabled.
Provides an option to ignore certain jobs from the monitoring process to focus on more critical or relevant jobs.

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:

Creates necessary temporary tables and populates them with data relevant to the job checks.
Analyzes each job against its last successful or failed run compared to the last check date.
Flags any job that has failed since the last check and compiles a report for review.
Updates monitoring metadata to keep the check state up to date.

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.