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
(
@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.
