VSTSC's Community Server

Community Server: The platform that enables you to build rich, interactive communities.
VSTeamSystemCentral: Your Team System community!

Welcome to VSTSC's Community Server Sign in | Join | Help
in Search

Steve Fibich

Wait! SQL Agent Job

This is the second in at least a two part series dealing with SQL Agent Jobs.  In this post, we are creating a new procedure to be used instead of the system procedure sp_start_job when you need to wait for a SQL Agent Job prior to moving on to the next step in a program.   You may need to do this if you have a procedure that calls a SQL Agent job, or a SQL Agent Job that calls a second SQL Agent Job or a separate job scheduler that is reaching over to a SQL Server to call a job, and I’m guessing there are other reasons, but these are the reasons I’ve come across. 

To accomplish this waiting on a SQL Agent Job we utilize the procedure introduced in part I of this series get_job_status_sp.   We use the built in sp_start_job procedure to call the SQL Agent Job as you normally would but we then use the get_job_status_sp to let us waitfor a job before returning control back to the calling processes.    There are a few other system stored procedures that are not documented in Books Online, so once again if this kind of thing makes you squeamish I understand.  There is some risk of using system procedures that are not documented because they could change any time without notice.  Unfortunately if you need this functionally you don’t have a lot of choice.  Outside of those items, this is basically just a while loop using the get_job_status_sp along with the waitfor command.  It’s pretty simple and pretty straight forward so if you feel the need to modify it in the future it should be easy to do so.

 One note of change to that procedure get_job_status_sp,  I have moved it into a new schema “jobs” inside the util database instead of leaving it in the dbo schema.  All of the new code is provided as well as the original on this website.

Here it is below: 

(Also available without commentary here: 0004_utilis.jobs.get_job_status_sp.sql ) 

(You may also want the util db build script here: 0001_create_database_util.sql  and the 0002_create_schemas_for_database_util.sql schema script)

(Finally the entire jobs schema will be kept up to date with each post at CompleteJobsSchema.zip)

 

use util

--======================--

--Logging some basic info

--======================--

print @@SERVERNAME

print SUSER_NAME()

print GETDATE()

go

 

:So the code above is just some standard logging information that I like to record when my scripts run incase I’m am writing the output to a log somewhere.  This was something I saw Andy Leonard do in one of his presentations and I think it’s a good idea, so I’ve started to incorporate it in all my scripts.  This will also be a good thing to have if you have an automated deployment environment or want to have one in the future. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

 

-- ==Depending on your build enviomrent you may want to comment out the destructive drop below== --

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[jobs].[start_job]') AND type in (N'P', N'PC'))

DROP PROCEDURE [jobs].[start_job]

 

: Nothing special above except for if you do not want to script out your permissions every time you run this script you may want to comment it out so you don’t accidently remove permissions on this proc.  When I run this script my account is either in the sysAdmin role or I script out the permissions in a separate file. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

 

GO

create proc jobs.start_job

@job_id UNIQUEIDENTIFIER = NULL

,@job_name SYSNAME= NULL

,@check_seconds int=59

as

 

: Notice that the procedure does not require either variable but it will need one; this is to be handled later in the script.  Some people like to have the comments below listed right before the create object statement so it is recorded in the system tables along with the object but not in it.  I like to have inside the object if I can. (Standard code disclaimer/blurb…sorry but I think it’s worth repeating)

 

 

-- =============================================== --

-- Author:Steve Fibich

-- Create date: 02/25/2010

-- Description: This procedure allows you to call

-- a SQL Agent Job and wait until it finishes. It

-- also waits for the called SQL Agent job to finish

-- if it is already running before it response to its

-- initial call.  This procedure has 3 variables; First

-- is the job_id the second is job name only one of these

-- can be populated and one of these must be populated.

-- The third is a truly optional varaible that determines

-- the interval with which the procedure will sleep while

-- waiting for a called SQL Agent job to finish, this by

-- default is set to 59 seconds.

--

-- Value  Description (From Books Online for SP_HELP_JOBS)

-- 0  Returns only those jobs that are not idle or suspended.

-- 1  Executing.

-- 2  Waiting for thread.

-- 3  Between retries.

-- 4  Idle.

-- 5  Suspended.

-- 7  Performing completion actions.

-- =============================================== --

BEGIN

SET NOCOUNT ON 

 

:SET NOCOUNT ON, why bother returning information about rows affected, we aren’t affecting any rows and this information would provided any value.

 

if ((@job_id is null and @job_name is null) or (@job_id is not null and @job_name is not null))

BEGIN

      RAISERROR(N'Either @job_id or @job_name is required.',10,1)

END

 

if @check_seconds >59

      BEGIN

            RAISERROR(N'@check_seconds must be less than 60. @check_seconds=%d',10,1,@check_seconds)

      END

 

: Ok the max value that is allowed for the check seconds variable is 59, and the reason for this is twofold.  First I think checking a job any longer than 60 seconds apart leaves a window that is two large, if I allowed the window to be 30 minutes or 23 hours this would allow this project to run for that long after the job had finished if the job just finished after the job status check.  If you leave the value for check status at 59 seconds and the job runs for an hour you have only check that status 60 times, which doesn’t seem too long to me.  Second, and this was one of the important reasons was changing a Integer to a string formatted for the wait command was just easier if it was less than 60.  Maybe in a future enhancement I’ll change it to take minutes and hours but so far this has served me well.

 

IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL)) 

BEGIN 

    EXECUTE msdb.dbo.sp_verify_job_identifiers

    '@job_name' 

    ,'@job_id' 

    ,@job_name OUTPUT 

    ,@job_id   OUTPUT 

END

 

:Calling sp_verify_job_identifiers gaurantees that we have a valid job id, it will over right our @job_id with a valid value if we only had a job_name and if we had a job_id it will overwrite it with the same value and populate the @job_name variable.

 

declare @job_status int

declare @len tinyint

declare @delay as char(8)

declare @check_seconds_vc char(2)

 

set @check_seconds_vc=cast(@check_seconds as char(2))

set @len=len(@check_seconds_vc)

set @delay = '00:00:00'

set @delay=stuff(@delay,9-@len,@len,@check_seconds_vc)

 

:Above is some logic to convert a integer value into a seconds value in the string format for the waitfor command.  Long story short I’m stuffing the @check_seconds variable converted into a charater string into a preformated string ’00:00:00’ based on the length of the check seconds string.

 

-- ==Intial Job check== --

execute jobs.get_job_status_sp

@job_id=@job_id

,@job_status=@job_status output

 

WHILE @job_status not in (4,5)

BEGIN

      waitfor delay @delay

      execute jobs.get_job_status_sp

      @job_id=@job_id

      ,@job_status=@job_status output

END

 

: This initial status check allows for this job to wait for a SQL Server Agent job if it is already running.   

 

-- ==Job is called; wait loop == --

if @job_status in (4,5)

BEGIN

--    print 'pre-execute'

      execute msdb.dbo.sp_start_job @job_id=@job_id

      waitfor delay '00:00:00'

--    print 'post-execute'

      execute jobs.get_job_status_sp

      @job_id=@job_id

      ,@job_status=@job_status output

--    print 'status' + cast(@job_status as char(10))

      WHILE @job_status not in (4,5)

      BEGIN

            waitfor delay @delay

            execute jobs.get_job_status_sp

            @job_id=@job_id

            ,@job_status=@job_status output

      END

END

 

: Finally the meat of the procedure.  After the initial check of the job status we know that SQL Agent job is not running.  At this point we execute the required job using the system procedure sp_start_job and then we check the status, who knows it could be a very fast running job, or it could have failed.   Note the waitfor delay ’00:00:00’  while this has a wait of nothing I found if I checked the job status right away with nothing else I could actually get the status before the job was started, but then a fraction of a second later it was running.  This tells me that there is some delay between starting a job with sp_start_job and getting the correct information back from the xp_sqlAgent_enum_jobs extended stored procedure which then also introduces this delay into my get_job_status_sp.  Also note that get_job_status_sp is now in the jobs schema. Then we wait if the job is running, which is what we expect.  Then check the job status and continue this wait/check loop until the job is finished.  That’s it that simple.

END --PROC

 

:TEST!:

: The code below tests the new procedure and insures it properly runs and waits for a SQL Agent job.  This test assumes you created the SlowlyRunningJob in part I of this series.

use util

--======================--

--Logging some basic info

--======================--

print @@SERVERNAME

print SUSER_NAME()

print GETDATE()

go

execute msdb.dbo.sp_start_job @job_name='SlowlyRunningJob'

go

execute msdb.dbo.sp_start_job @job_name='SlowlyRunningJob'

-- ==Failure after this section runs because the job is already running== --

go

 

execute jobs.start_job

@job_name='SlowlyRunningJob'

,@check_seconds=2

go

execute msdb.dbo.sp_start_job @job_name='SlowlyRunningJob'

-- ==Success after this section runs== --

 

If anyone knows how to accurately or more accurately get the status of a job without using xp_sql_agent_enum jobs please let me know.

 

Published Sunday, March 14, 2010 11:43 AM by steve
Filed under: ,

Comments

 

Delivery job steve said:

December 21, 2010 12:28 PM
Anonymous comments are disabled
Powered by Community Server (Personal Edition), by Telligent Systems