SQL Server Monitoring - Agent jobs configuration

This blog is part of the blog series named SQL Server Monitoring.

Purpose

To list the configured agent jobs and their configuration.

Report

The report has 3 tables, where the table in the top half shows the daily and weekly recurring jobs, the table in the bottom left shows jobs with special schedules, and the table in the bottom right shows jobs without schedules.

Sources

This feature queries the MSDB tables dbo.sysjobs, dbo.sysjobschedules and dbo.sysschedules which return information for the agent jobs and their schedules.

Model

The model for the report uses the 3 facts [Agent jobs – grid], [Agent jobs – list] and [Agent jobs – not scheduled], as well as the dimension [Agent job].

Service setup

The feature uses the service ‘fhsmSPAgentJobs’ which can be configured using the stored procedure fhsmSPControl.

Parameter

There is no parameter to be configured for this service.

Schedule

The default schedule for the service is once every day between 06:00 and 07:00.

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'list',
	@Task = 'fhsmSPAgentJobs',
	@Name = 'Agent jobs',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Schedule',
	@Command = 'set',
	@Task = 'fhsmSPAgentJobs',
	@Name = 'Agent jobs',
	@ScheduleType = 0,
	@Enabled = 1,
	@ExecutionDelaySec = 43200,
	@FromTime = '06:00:00',
	@ToTime = '07:00:00',
	@Monday = 1,
	@Tuesday = 1,
	@Wednesday = 1,
	@Thursday = 1,
	@Friday = 1,
	@Saturday = 1,
	@Sunday = 1;

Retention

The default retention for the table dbo.fhsmAgentJobs is 90 days.

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'list',
	@TableName = 'fhsmAgentJobs',
	@Wildcard = 0;

EXEC fhsmSPControl
	@Type = 'Retention',
	@Command = 'set',
	@TableName = 'fhsmAgentJobs',
	@Enabled = 1,
	@Sequence = 1,
	@Days = 90;

SQL Server Monitoring – Agent jobs configuration