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.

The *Suspend Job* context menu item and its immediate options.

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

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

Additionally, 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 the Job Monitor toolbar in case you decide otherwise.

Error creating thumbnail: Unable to save thumbnail to destination

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.

Suspending a Job

From the context menu of any job that is not currently suspended, the following options are available from the Suspend Job menu item.

1 hour
4 hours
6 hours
12 hours
24 hours
2 days
7 days
30 days
Custom

Selecting Custom allows picking the date and time in which to resume operation of the selected job.

Error creating thumbnail: Unable to save thumbnail to destination

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. Since the suspended job is put into a disabled state, the metric Jobs: Disabled will be incremented by the number of jobs suspended.

Resuming a 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.

Error creating thumbnail: Unable to save thumbnail to destination

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, therefore proper communication with your team is recommended.

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 from within SQL Agent Insight that has been suspended is a valid option and will automatically remove the job from the suspension queue.

Viewing all Suspended Jobs

From within Job Monitor clicking the Show suspended / paused jobs button in the toolbar will create a filter and only suspended jobs will be shown in the job pane.

Alternatively, clicking on the Suspended total in the Job Monitor Summary will also filter the jobs in the job pane with focus on suspended jobs.

The pertinent columns for suspended jobs within the job pane are:

Suspended When - indicates when the job was paused within SQL Agent Insight
Resume When - indicates when the job will resume it's pre-suspended state.
Suspended By - indicates who suspended the job.

Removing the Ability to Suspend Jobs

If the desire is to remove the capability of suspending jobs and resuming them automatically, this can be done via the Job Monitor toolbar by clicking on the Remove custom suspend/pause jobs from server instance button.

Upon doing so, a dialog will appear confirming the action notifying the user what is going to be done. In this Remove suspend/resume jobs dialog, if there are currently any jobs in a suspended state they will be listed near the bottom.

Clicking Yes will remove the custom category, schedule and jobs for the suspend/resume functionality. Additionally, if there are currently jobs in a suspended state, they will be put into their pre-suspension state, effectively resuming them.

The dialog confirming the removal of the suspend/pause job functionality of *SQL Agent Insight*

Back to Documentation Main

Microsoft®, Windows® and SQL Server® are registered trademarks of Microsoft Corporation in the United States and/or other countries.
Some icons courtesy of Icons8