Sql script that waits until job is finished
In one of my projects I needed to disable certain SQL job from stored procedure and wait until that job is idle. I've been looking around a while and then I quickly build up own code and I hope someone will find it useful - here it is:
--
=============================================
-- Author: Radim Hampel
-- Create date: 2008-05-06
-- Description: Enables/Disables specified job and
waits until it is finished(idle)
-- This procedure is used from FullOptimize procedures
-- =============================================
ALTER Procedure [dbo].[xsvk_ChangeJobStatus]
(
@JobName varchar(50)
,@Enabled
int
,@WaitUntilJobFinished int = 1
,@WaitCycleTime varchar(30) = '00:00:01'
)
-- Change status of a job
UPDATE MSDB.dbo.sysjobs SET Enabled = 0 WHERE Name
= @JobName
-- If job is about to be stopped and we want to wail
until it finishes
If(@Enabled = 0 and
@WaitUntilJobFinished = 1)
begin
CREATE
TABLE #job_status
(
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
)
Declare
@IsSysadmin int, @Owner nvarchar(30), @JobId uniqueidentifier,
@Status int
select
@Status = 0,
@IsSysadmin = 1, @Owner = Suser_Sname(),
@JobId = job_Id from msdb.dbo.sysjobs where name=@JobName
-- Wait until
job is finished, 1 = Executing, 2 = Waiting For Thread, 3 = Between Retries, 4 =
Idle, 5 = Suspended, 6 = [obsolete], 7 = PerformingCompletionActions
while(@Status <> 4)
begin
-- Check
the status
insert into
#job_status EXECUTE master.dbo.xp_sqlagent_enum_jobs @isSysadmin, @owner, @JobID
select @Status =
Job_State from #job_status
delete from
#job_status
-- Waits
specified about of time
WaitFor Delay
@WaitCycleTime
end
drop
table #job_status
end