SQL Server Agent Jobs: Executing, monitoring and troubleshooting guide

1. Introduction and Purpose

This document serves as a guide to support users in identifying, executing and understanding the status and scheduling of SQL Server Agent Jobs used for DUK ETL tasks.

Note: The guide is aimed at users needing to monitor and troubleshoot existing ETL jobs and not the creation or modification of jobs

2. Overview

SQL Server Agent is a Windows service that functions as a part of Microsoft SQL Server, used for scheduling and executing tasks packaged as jobs. A job contains one or more steps, each step representing a task such as running a script or command.

For the ETL tasks in our environment, jobs are configured to use the CmdExec subsystem of the SQL Server Agent, allowing the execution of command-line applications and scripts.

In our case, CmdExec executes the PowerShell (PS) scripts stored on the server machine, which performs the ETL tasks.

The subsequent sections in this document will provide a guide to:

executing a job manually
identifying relevant jobs
checking their current status
accessing their history logs
reviewing their schedules

3. Finding Relevant SQL Server Agent Jobs

SQL Server Agent jobs can be found in SQL Server Management Studio (SSMS) under the SQL Server Agent node in the Object Explorer.

Below are the steps to access them:

Launch SQL Server Management Studio (SSMS) and connect to the gs1dukmsqlv03 server.
Once connected, find and expand the SQL Server Agent node in the Object Explorer.
Expand the Jobs node.

Here you can find a list of all the jobs created in the SQL Server instance. The ETL jobs use the naming convention: job_<Prefix>_<DescriptiveName> (e.g., Job_ETL_001_ExportToSterling)

To view the properties of a job, right-click on the job and select Properties.

4. Steps to Execute a SQL Agent Job Manually

Navigate to the SQL Server Agent Node

In the Object Explorer, expand the SQL Server Agent node.
Under SQL Server Agent, expand the Jobs node. Here you will see a list of all jobs configured on the server.

Identify the Job to Execute

Find the job you want to execute. The ETL jobs follow a naming convention such as job_<Prefix>_<DescriptiveName> (e.g., job_ETL_004_MembershipRenewalFiles).

Right-click on the job and select Start Job at Step….

Start the Job

In the Start Jobs dialog that appears, you can select the step from which to start the job. By default, it starts from step 1.
Click Start.

Monitor the Job Execution

You can monitor the job's progress in the Job Activity Monitor.
Right-click on SQL Server Agent and select Job Activity Monitor.
In the Job Activity Monitor, you will see the job status change from Idle to Executing (or similar states) as it runs.

5. Checking Job Status

You can check the current status of a job using the Job Activity Monitor

Navigate to the SQL Server Agent node.
Right-click on the SQL Server Agent node and select Job Activity Monitor

This will open a new window that provides a live view of the status of all jobs. For each job, you can see its current state (Idle, Running, or Suspended), the last run outcome, and the next scheduled run time.

To refresh the view, right-click within the Job Activity Monitor and select Refresh. To view more details about a specific job, you can double-click on the job to open its properties window.

6. Accessing Job History Logs

The job history log records the execution of the job, providing information such as when the job started, when it ended, and whether it was completed successfully.

To access the job history log:

Navigate to the job under the Jobs node.
Right-click on the job and select View History from the context menu.

This will open a window containing a detailed history of job execution instances. The history will include the job outcome, the duration of the job, and any messages generated during the job execution.

7. Reviewing Job Schedules

Here's how you can check the scheduling for a specific job:

In SSMS, navigate to the Jobs node under the SQL Server Agent node.
Right-click on the job whose schedule you want to check and select Properties.
In the Job Properties window that opens, click on the Schedules page in the select pane on the left.

Here, you can see all schedules assigned to the job. Each schedule will be listed with its name, enabled status, type, frequency, and other details.

Details on a schedule include:

Name: The name of the schedule.
Enabled: Whether the schedule is currently active.
Type: The type of schedule
Frequency: Details on how often the schedule runs. This could be daily, weekly, or monthly and at what time or times.

Document Properties

Property Value Version
0.02 Created
2023-06-06 Last updated 2024-06-09 Author Michael Geelan