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.
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.
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
Selecting Custom allows picking the date and time in which to resume operation of the selected job.
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.
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.
Microsoft®, Windows® and SQL Server® are registered trademarks of Microsoft Corporation in the United States and/or other countries.
Some icons courtesy of Icons8