Vítejte na blog.vyvojar.cz Přihlásit | Registrovat | Pomoc


Radim Hampel : Microsoft business intelligence, performance management and data warehousing
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)


      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)


            -- 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


      drop table #job_status


Zveřejněno 7. května 2008 17:55 by radim

Vedeno pod:


Žádné komentáře

Nejsou povoleny nové komentáře k tomuto příspěvku
Vyvojar.cz na prodej!