Debunking the Myths: Cloud HA and DR common misconceptions

Learn more about SQL Server tools



solving sql server problems for millions of dbas and developers since 2006 join MSSQLTips for free SQL Server tips






Learn more about SQL Server tools








Learn more about SQL Server tools


   Got a SQL tip?
            We want to know!

Custom sp_start_job to delay next task until SQL Agent Job has completed

MSSQLTips author Kun Lee By:   |   Read Comments (10)   |   Related Tips: More > SQL Server Agent
Problem

The system stored procedure sp_start_job is a very useful T-SQL command that starts a SQL Agent job. The problem with this is that it starts the job and it doesn't wait until the job is finished and sometimes I want to start a job and wait until the job is finished and move on to another job or task that depends on the result of the previous job.

For example, in one of my previous tips, "Maintain Customized Indexes on Replication Subscriber Tables via Pre and Post Scripts", in the case of the Pre Snapshot job, I want to make sure the Pre Snapshot job is finished before I move on to the next step. Another example, we are using Change Data Capture on replication subscribers and I want to break the Change Data Capture before I apply the snapshot and there is a job that takes care of that, so I want to run that job first and make sure the job has completed before I move on to next step.

Solution

I have created a custom stored procedure called "sp_start_job_wait" and basically it starts a job by using sp_start_job and checks if the job is finished using a loop. You can download the stored procedure here.

Let's take a look at a couple of examples.


Example 1

This is the typical use of sp_start_job_wait. This will run job "zzzDBATest" and then by default, it checks the job status every 5 seconds and exits based on the job completion status.

-- Starts a job call zzzDBATest  
exec sp_sp_start_job_wait 'zzzDBATest'

When the job succeeded, the output looks like this.

Job 'zzzDBATest' started successfully.  
JOB IS RUNNING
JOB IS RUNNING
JOB IS RUNNING
The job ran Successful

When the job failed, the output looks like this.

Job 'zzzDBATest' started successfully.  
Msg 50000, Level 16, State 1, Procedure sp_sp_start_job_wait, Line 76 [ERROR]:zzzDBATest job is either failed or not in good state. Please check

When the job is canceled, the output looks like this.

Job 'zzzDBATest' started successfully.  
JOB IS RUNNING
JOB IS RUNNING
The job is Cancelled

Example 2

You can also specify parameters like this if you know the job takes longer to finish and you don't want to check every 5 seconds. In this example we are checking every 5 minutes.

-- Starts a job call zzzDBATest
DECLARE @RetStatus int  
exec sp_sp_start_job_wait
@job_name = 'DBA - Test Job',
@WaitTime = '00:05:00',
@JobCompletionStatus = @RetStatus
OUTPUT select @RetStatus


Code

Here is the stored procedure and here are some points regarding the proc. Again, you can download the stored procedure here.

  • It uses the xp_sqlagent_enum_jobs store procedure to check the status of the job
  • I used the "raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT" to print out the status if it is running or not right away while I am running from SQL Server Management Studio. This way I can see if it is working or hung.
  • It queries the sysjobhistory table
CREATE PROCEDURE dbo.sp_sp_start_job_wait
(
@job_name SYSNAME,
@WaitTime DATETIME = '00:00:05', -- this is parameter for check frequency
@JobCompletionStatus INT = null OUTPUT
)
AS

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

-- DECLARE @job_name sysname
DECLARE @job_id UNIQUEIDENTIFIER
DECLARE @job_owner sysname

--Createing TEMP TABLE
CREATE TABLE #xp_results (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE name = @job_name

SELECT @job_owner = SUSER_SNAME()

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

-- Start the job if the job is not running
IF NOT EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
EXEC msdb.dbo.sp_start_job @job_name = @job_name

-- Give 2 sec for think time.
WAITFOR DELAY '00:00:02'

DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
BEGIN

WAITFOR DELAY @WaitTime

-- Information
raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT

DELETE FROM #xp_results

INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id

END

SELECT top 1 @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id
and step_id = 0
order by run_date desc, run_time desc

IF @JobCompletionStatus = 1
PRINT 'The job ran Successful'
ELSE IF @JobCompletionStatus = 3
PRINT 'The job is Cancelled'
ELSE
BEGIN
RAISERROR ('[ERROR]:%s job is either failed or not in good state. Please check',16, 1, @job_name) WITH LOG
END

RETURN @JobCompletionStatus

GO

There you have it. Now, by using this proc I can control jobs and job dependencies easily. By using this process, I was able to make post-snapshot tasks a dependency with the replication script.

I'd like to thank to Srikant Tadimalla and Sourav Biswas for feedback and changes to some of the code to make it work with SQL 2000 as well.

Next Steps
  • You can change the stored proc to have additional parameters like sp_start_job does by adding @job_id, @step_name, etc...
  • You can change the stored proc to call Sql Server remotely by putting the @remote_server_name parameter and either using xp_cmdshell with sqlcmd or OPENQUERY
  • Download the procedure and implement this in your environment where you have a need to control job dependencies.


Last Update: 11/18/2010


About the author
MSSQLTips author Kun Lee
Kun Lee is a database administrator and his areas of interest are database administration, architecture, data modeling and development.

View all my tips
Related Resources


print tip Print  
Become a paid author





join MSSQLTips for free SQL Server tips     



Comments and Feedback:
Thursday, November 18, 2010 - 8:41:06 AM - Steve hogg Read The Tip

This is awesome! Thanks so much for sharing.


Wednesday, December 01, 2010 - 2:49:47 PM - ksturg53 Read The Tip

Good stuff!  I wonder if you could assist with implementing this into an SSIS package. I'd like to add the step to set off the job and then not have the following step in the package run until the job is complete. Ideas?


Friday, December 03, 2010 - 8:40:11 PM - Kun Lee Read The Tip

 This should be easy. From SSIS Package, you can use "Execute SQL Task" and call the store procedure directly like "exec sp_sp_start_job_wait 'zzzDBATest'" and make sure to configure to move next step when it succeeded only.

 

Please let me know if this wasn't what you were looking for.

Regards,

Kun


Friday, February 11, 2011 - 8:47:27 AM - juergen Read The Tip

The query for the completition status should be changed - because here you can get a resultset and your infomation about might be wrong!

I would recommend following - if you want to get the completition status for the whole job (not only single steps):

SELECT top 1 @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id
and step_id = 0
order by run_date desc, run_time desc


instead of


SELECT @JobCompletionStatus = run_status
FROM msdb.dbo.sysjobhistory
WHERE job_id = @job_id


Tuesday, February 22, 2011 - 8:33:20 AM - Kun Lee Read The Tip

Hi juergen

Thank you for your comment and you are right about that. I am going to fix the article.

thank you so much for recommendation.

Regards,

Kun

 


Wednesday, December 21, 2011 - 1:42:11 PM - Derf Read The Tip

That is amazingly usefull. Thanks for sharing.


Monday, June 11, 2012 - 4:14:42 PM - Aaron Sentell Read The Tip

Great little piece of code. I could have used this many times in the past. Thanks for sharing it.

Aaron


Thursday, November 22, 2012 - 11:21:46 PM - Rafi Read The Tip

good job.solved all my issues.

Thanks for sharing Kun


Wednesday, December 04, 2013 - 11:50:45 AM - Paul-Benjamin TOUZET Read The Tip

Hello,

when I use your procedure with a remote server I have the following error:

Error 18483: Could not connect to server because "" is not defined as a remote login at the server.

The only change I have done is to use my remote server like this:

INSERT INTO #xp_results EXECUTE SIE48.master.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id where SIE48 is my remote server.

 

This is this line who raise the error.

 

Thanks in advance for your help,

Paul-Benjamin


Monday, January 27, 2014 - 4:57:46 AM - ledub Read The Tip

Great, it's very useful to synchronize maintenance plans, backup, etc ...

thanks a lot, guy



Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name   *Email Notify for updates



Comments
Get free SQL tips:

*Enter Code refresh code


 

Sponsor Information







Copyright (c) 2006-2014 Edgewood Solutions, LLC All rights reserved
privacy | disclaimer | copyright | advertise | about
authors | contribute | feedback | giveaways | free t-shirt | user groups | community | events | first timer?
Some names and products listed are the registered trademarks of their respective owners.