Revision as of 01:20, 16 February 2023

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.

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

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

Viewing all Suspended Jobs

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

@@ Line 76: / Line 76: @@
 |}
 |}
+== Resuming a Job ==
 == Viewing all Suspended Jobs ==