Agent Jobs Check

Job details

Name:	Agent Jobs Check
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:	Checks whether there exists jobs on the SQL server which have not been executed, or have failed during execution.
Version:	2.8
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. You can use % (percent sign) to represent wild card characters.
ignore not scheduled jobs	YES	If set to “YES“ jobs without schedule (or with disabled schedules) will be ignored.
ignore disabled jobs	YES	This parameter allows you to control whether disabled jobs should be ignored.
ignore old jobs	48	This parameter allows you to ignore failed jobs executed some time ago (default 48 hours ago).
ignore step error	YES	This parameter allows you to ignore step errors.
ignore if last run successful	YES	Jobs which failed (within the timeframe defined by the parameter “ignore old jobs”) but run successfully when last executed will be ignored.
return status	1	Return status value (ALARM – 2, WARNING – 1, or OK – 0) when failed jobs detected.

Job Summary

Purpose: The purpose of this monitoring job is to check the execution status of SQL Agent Jobs within SQL Server instances.
Why: This job is crucial because it ensures that all scheduled SQL Agent jobs are performing as expected and provides an alert if any job fails, has not been executed, or is scheduled but disabled. It safeguards against potential failures that may interrupt or impair database operations and processes.
Manual checking: You can manually verify the job status by using the following SQL commands:

SELECT job.name, job_history.run_status, job_history.run_date FROM msdb.dbo.sysjobs job INNER JOIN msdb.dbo.sysjobhistory job_history ON job.job_id = job_history.job_id ORDER BY job_history.run_date DESC;

Job Details

Type: The job focuses on SQL Server instances, specifically those with major version numbers greater than 2000, ensuring they have the SQL Server engine and are Microsoft SQL Server editions.
Components: The job queries and uses data primarily from the msdb database, including:
- sysjobs (stores details about each job)
- sysjobhistory (contains history of job execution)
- sysjobschedules (provides scheduling details for each job)
- sysschedules (stores schedule details used by SQL Server Agent)

The job includes parameters (which have been excluded from this discussion as per instructions) that allow administrators to adjust which jobs are considered during the checks, such as ignoring disabled jobs, not scheduled jobs, and specific old job executions based on specified criteria.

Monitoring Strategy

The job utilizes a complex SQL procedure to monitor, filter, and report on the job execution statuses. It verifies:
- If the job was executed successfully.
- Jobs that have failed during execution and those that haven’t been executed at all after being scheduled.

Implementation:
- If any job or job step fails (conditions based on parameters set), it adjusts the return status to represent an ALARM, WARNING, or OK based on severity.
- Also, using dependency checks from various msdb tables, it ensures that all necessary job information is accurate and updated, avoiding false alarms.

Reporting and Presentation

Presentation Layer: Through various SQL SELECT calls embedded in the job’s XML configuration, it prepares comprehensive reports about:
- Overall job execution status
- Specific errors encountered during the job steps
- Scheduled but inactive jobs, such as those disabled or not yet run

Each presentation section is designed to offer clear insights into job performance, status updates, last executions, and modifications made to the job setups.

Deployment Considerations

The job is configured for automatic installation and forceful updating to ensure all instances run the most current monitoring logic.
Post-update or initial setup, the job creates necessary supporting tables and stored procedures required for this specific monitoring.

This monitoring job is essential for maintaining operational stability and proactive administration in environments dependent on SQL Server Agent for executing scheduled tasks and workflows.