Applies to: SQL Agent Insight version 0.22 SQL Agent Insight version 0.23

Suspending a Job

The functionality of suspending a job is a unique feature within SQL Agent Insight that other products do not have. Suspending a job will pause the selected job from running until the selected future date and time chosen by the user at which point the job will automatically resume.

Why suspend a job?

Have you ever…

  • disabled a job and forgotten to enable it again?
  • wanted to put a job into maintenance without creating complex schedules or assigning multiple schedules to a job?
  • wanted to restore a database and ensure jobs that run against that database are paused?
  • do troubleshooting and want to minimize potential system overhead by performance intensive jobs?

If the answer is yes to any of those questions, you may benefit from the suspension / pausing of SQL agent jobs.

Prerequisites

Create the suspend/resume jobs dialog.
Click to enlarge.

Opening Job Monitor the first time will show the creation of the suspend/resume jobs dialog.

If you choose ‘Yes’, jobs with the following names will be created:

_SQL Agent Insight – Job Controller
_SQL Agent Insight – Suspended Jobs

along with a job schedule by the name of [_SQL Agent Insight – Job Controller] that fires every 10 seconds (by default). Additionally a new job category, [SQL Agent Insight] will also be established.

If you choose ‘No’, the option to suspend jobs will NOT be available and you can access this dialog again by clicking on the [Show suspended/paused jobs] button in Job Monitor in case you decide otherwise.

■ NOTE
SQL Agent Insight does NOT have to be running for jobs to resume at their chosen times; this is the purpose of the noted ‘controller’ job. Jobs that are paused (maintenance mode), are listed in the _SQL Agent Insight – Suspended Jobs job.

If you choose to create the aforementioned objects, a new option in the Job Monitor toolbar will be available to remove them if desired, putting all suspended jobs back into their original state on the instance.

It is NOT recommended to modify any of these 4 objects without first reaching out to Brentec support. Tweaking these objects may leave jobs in an undesirable state and potentially unable to execute in the future.

In the background, technically, when a job is “suspended” it is actually disabled, storing the data in 2 controller jobs within the monitored instance.

From the context menu of any job that is not currently suspended, the following quick options are available. Alternatively, to select an option not in the context menu, select “Custom” to pick the date and time in which to resume operation of the selected job.

  • 1 hour
  • 4 hours
  • 6 hours
  • 12 hours
  • 24 hours
  • 2 days
  • 7 days
  • 30 days
  • Custom
■ NOTE
Suspending a job will keep track of the jobs current state upon resuming. That is, if the job was disabled at the time of suspension, the job upon resumption will also be in a disabled state.
Conversely, a job that was in an enabled state will technically be disabled while suspended, however, when the job is resumed, it will be put back into an enabled state.

RESUME JOB

Resuming a job is a unique feature within SQL Agent Insight that does not exist in other products. The job must previously have been suspended for this option to be available.

Resuming a job will immediately remove the job from the suspend/resume queue and put the selected job to its original state at the time of suspension. See the note above.

■ ATTENTION
Since SQL Agent Insight effectively only disables the job and stores the state of the job at the time of suspension, there is a possibility that another user could enable the job directly on the server prior to SQL Agent Insight resuming the job itself.
In the case of an enabled job being suspended this is not an issue upon resuming. However, if a disabled job was suspended and it was later enabled outside of SQL Agent Insight, upon resuming, SQL Agent Insight would set the job back to a disabled state, i.e., its original state at the time of pausing.

This could potentially be a problem and will be addressed in a future version.

In either case, the monitored SQL Agent Insight metrics for Enabled Jobs and Disabled Jobs will change accordingly and will notify the user.

Care should be taken when suspending an already disabled job, especially if it is possible that another user could enable it from outside of SQL Agent Insight.

Additionally, enabling a job that has been suspended from within SQL Agent Insight is a valid option and will automatically remove the job from the suspension queue.
The Suspend Job context menu items and its immediate options.
Click to enlarge.

Viewing all Suspended Jobs

From within Job Monitor clicking the Show suspended / paused jobs button will open the Suspended / Paused Jobs dialog.
As long as the dialog is open, it will auto-refresh every 30 seconds or when Job Monitor is updated, whichever should come first.

Once open, pertinent information regarding all paused jobs is shown including:

  • the job name,
  • when the job was suspended,
  • when the job will automatically resume, and,
  • who suspended the job.

Furthermore, the row header will have a colored band indicating the state of the job at the time of the suspension. Red indicates the job was disabled when paused, whereas green indicates the job was enabled when the job was suspended.
When the job automatically resumes, the job state is changed accordingly.

The Suspended / Paused Jobs dialog.
Click to enlarge.