Sql Server Does a Job Wait to Complete Before Starting Again
By: | Updated: 2010-11-18 | Comments (17) | Related: > SQL Server Agent
Problem
The organisation stored process sp_start_job is a very useful T-SQL control that starts a SQL Agent job. The problem with this is that information technology starts the job and it doesn't wait until the job is finished and sometimes I desire to beginning a chore and wait until the job is finished and motility on to some other 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 task, I want to make sure the Pre Snapshot job is finished before I move on to the next stride. Some other example, we are using Change Data Capture on replication subscribers and I want to break the Change Data Capture before I utilize the snapshot and at that place is a job that takes care of that, so I want to run that job commencement and make sure the task has completed earlier I move on to next step.
Solution
I have created a custom stored procedure called "sp_start_job_wait" and basically it starts a chore by using sp_start_job and checks if the job is finished using a loop. Yous tin can download the stored procedure here.
Permit's take a look at a couple of examples.
Example ane
This is the typical utilize of sp_start_job_wait. This will run job "zzzDBATest" then by default, it checks the task status every five 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.
Chore 'zzzDBATest' started successfully.
Task IS RUNNING
JOB IS RUNNING
JOB IS RUNNING
The job ran Successful
When the chore failed, the output looks like this.
Job 'zzzDBATest' started successfully.
Msg 50000, Level 16, Country one, Procedure sp_sp_start_job_wait, Line 76 [Fault]:zzzDBATest job is either failed or not in good country. Please check
When the job is canceled, the output looks like this.
Job 'zzzDBATest' started successfully.
Task IS RUNNING
JOB IS RUNNING
The chore is Cancelled
Instance 2
You tin also specify parameters like this if you know the job takes longer to stop and you don't desire to bank 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 - Exam 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, y'all tin download the stored process here.
- It uses the xp_sqlagent_enum_jobs shop procedure to check the status of the job
- I used the "raiserror('JOB IS RUNNING', 0, i ) WITH NOWAIT" to impress out the status if it is running or not right abroad while I am running from SQL Server Management Studio. This style I can see if it is working or hung.
- Information technology queries the sysjobhistory table
CREATE PROCEDURE dbo.sp_sp_start_job_wait
(
@job_name SYSNAME,
@WaitTime DATETIME = '00:00:05', -- this is parameter for bank check frequency
@JobCompletionStatus INT = null OUTPUT
)
AS
Gear up TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
Gear up 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 Non Nix,
last_run_time INT NOT NULL,
next_run_date INT NOT Cipher,
next_run_time INT NOT Zippo,
next_run_schedule_id INT Non Goose egg,
requested_to_run INT NOT NULL, -- BOOL
request_source INT Non Cipher,
request_source_id sysname COLLATE database_default Naught,
running INT NOT Nada, -- BOOL
current_step INT Non NULL,
current_retry_attempt INT Non NULL,
job_state INT Non Zero)
SELECT @job_id = job_id FROM msdb.dbo.sysjobs
WHERE proper noun = @job_name
SELECT @job_owner = SUSER_SNAME()
INSERT INTO #xp_results
EXECUTE principal.dbo.xp_sqlagent_enum_jobs one, @job_owner, @job_id
-- Starting time the task if the job is non running
IF Not EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = i)
EXEC msdb.dbo.sp_start_job @job_name = @job_name
-- Give 2 sec for call up time.
WAITFOR Filibuster '00:00:02'
DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE master.dbo.xp_sqlagent_enum_jobs one, @job_owner, @job_id
WHILE EXISTS(SELECT TOP 1 * FROM #xp_results WHERE running = 1)
BEGIN
WAITFOR Filibuster @WaitTime
-- Information
raiserror('JOB IS RUNNING', 0, 1 ) WITH NOWAIT
DELETE FROM #xp_results
INSERT INTO #xp_results
EXECUTE principal.dbo.xp_sqlagent_enum_jobs 1, @job_owner, @job_id
END
SELECT tiptop 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 = three
PRINT 'The job is Cancelled'
ELSE
Begin
RAISERROR ('[ERROR]:%s job is either failed or not in expert land. Please check',sixteen, one, @job_name) WITH LOG
Finish
Return @JobCompletionStatus
Go
There you have it. Now, past 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 similar to give thanks to Srikant Tadimalla and Sourav Biswas for feedback and changes to some of the code to make it work with SQL 2000 as well.
Adjacent Steps
- Yous tin can change the stored proc to have additional parameters like sp_start_job does by adding @job_id, @step_name, etc...
- You can alter the stored proc to phone call Sql Server remotely past 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 yous have a demand to command chore dependencies.
Related Articles
Popular Articles
About the author
Kun Lee is a database administrator and his areas of interest are database assistants, compages, data modeling and development.
View all my tips
Article Final Updated: 2010-11-18
Source: https://www.mssqltips.com/sqlservertip/2167/custom-spstartjob-to-delay-next-task-until-sql-agent-job-has-completed/
0 Response to "Sql Server Does a Job Wait to Complete Before Starting Again"
Post a Comment