Home Dashboard Directory Help
Search

Add "synchronous_wait" option to sp_start_job by Joseph Sack


Status: 

Closed
 as Won't Fix Help for as Won't Fix


18
0
Sign in
to vote
Type: Suggestion
ID: 434754
Opened: 4/23/2009 11:03:43 AM
Access Restriction: Public
1
Workaround(s)
view

Description

The sp_start_job system stored procedure is executed asynchronously. For example, executing a job that will execute for five hours, using sp_start_job results in an immediate return of control back to the caller while the job continues to run in the background.

Although this is desirable in several scenarios, there are some reasons why an end-user or process would want to wait synchronously for control to be returned after executing sp_start_job.

In my customer's situation, they have a centralized SQL Server Agent job that executes two steps sequentially. Each step exeecutes a job using sp_start_job. The first step is used to initiate a database backup operation. After the database backup operation is completed, the second step is to wait for the backup to finish, and then kick off a network backup of the database backup device.

Because sp_start_job runs asynchronously, they have to manually create a "listener" job step to loop until the job status is complete. They would rather have the option to wait synchronously for the database backup operation to complete prior to completing the job step, and then move to the next job step in the SQL Server Agent job depending on success or failure.
Details
Sign in to post a comment.
Posted by Frenk van Beekveld on 3/4/2011 at 6:02 AM
Why is this Closed as Won't Fix then?
Posted by Microsoft on 4/19/2010 at 3:24 PM
We will consider this for our next major release.

Thanks,

Amy Lewis
Posted by Microsoft on 4/19/2010 at 3:24 PM
We will consider this for our next major release.

Thanks,

Amy Lewis
Posted by Microsoft on 4/30/2009 at 8:42 AM
Hi,

This suggestion makes a lot of sense and we actualy heard it from other sources. We will consider it for the next release.

Thanks,

Gil
Sign in to post a workaround.
Posted by Kael Dowdy on 5/25/2012 at 10:15 AM
While not an optimal solution on passing a wait/true parameter to the sp_start_job to make it run synchronously, what if you had a flag in a table that you can examine...

For example, create a table which contains a flag to determine if the job is running. For the first job step, update the flag to true. Then in the 2nd (and subsequent) job step(s), call whatever procedure/process you are currently running in the job. Then in a final job step, update the flag to false.

Then, in the stored procedure that you call the sp_start_job currently, go ahead and call the procedure as normal, but then have a loop that will check the flag...and don't exit the loop until the flag is set back to false. You may also need a WAITFOR command in there to pause execution before the loop to be sure to give the job enough time to kick off.

Maybe this will give you some ideas...