Click here to Skip to main content
Click here to Skip to main content

One At A Time SQL Agent Job

, 12 Nov 2013
Rate this:
Please Sign up or sign in to vote.
A script that looks to see if there are any instances of job that are still running and if it does not find one it starts one.

Situation

Recently I was on a project where I had an SSIS package that was doing a fair amount of ETL work and based on the projected data load it was expected to take 10-15 minutes for an average load of data.  The problem with this is the job had to start based on some user interaction and the user needed the results ASAP.  Because of the complexity of the ETL I did not want to rely on transactional boundaries to keep the straight because data is being written to and from multiple databases.  The transactions would essentially put locks on almost any table in multiple databases which is not ideal.

Solution

Instead I decided to try and query the system tables to see if an instance of the SQL Agent job in charge of running this package is already started.  I had queried system tables for table and database metadata before but never for job statuses.  As it turns out this is a fairly straight forward task, after a little search the web and a bit of trial and error I had the following stored procedure.

<span class="rem">-- =============================================</span>
<span class="rem">-- Author:          JJ Bussert</span>
<span class="rem">-- Create date:     2013-11-11</span>
<span class="rem">-- Description:     www.scrider.com</span>
<span class="rem">-- =============================================</span>
<span class="kwrd">CREATE</span> <span class="kwrd">PROCEDURE</span> [dbo].[StartOneAtATime]
<span class="kwrd">AS</span>
<span class="kwrd">BEGIN</span>
    <span class="kwrd">SET</span> NOCOUNT <span class="kwrd">ON</span>;

    <span class="kwrd">DECLARE</span> @JOB_NAME SYSNAME = N<span class="str">'SQL_Agent_Job'</span>; 
 
    <span class="kwrd">IF</span> <span class="kwrd">NOT</span> <span class="kwrd">EXISTS</span>(     
            <span class="kwrd">SELECT</span> 1 
            <span class="kwrd">FROM</span> msdb.dbo.sysjobs_view job  
            <span class="kwrd">INNER</span> <span class="kwrd">JOIN</span> msdb.dbo.sysjobactivity activity 

                <span class="kwrd">ON</span> job.job_id = activity.job_id 
            <span class="kwrd">WHERE</span>  
                activity.run_Requested_date <span class="kwrd">IS</span> <span class="kwrd">NOT</span> <span class="kwrd">NULL</span>  
            <span class="kwrd">AND</span> activity.stop_execution_date <span class="kwrd">IS</span> <span class="kwrd">NULL</span>  
            <span class="kwrd">AND</span> job.name = @JOB_NAME 
            ) 
    <span class="kwrd">BEGIN</span>      
        <span class="kwrd">PRINT</span> <span class="str">'Starting job '</span><span class="str">''</span> + @JOB_NAME + <span class="str">''</span><span class="str">''</span>; 
        <span class="kwrd">EXEC</span> msdb.dbo.sp_start_job @JOB_NAME; 
    <span class="kwrd">END</span> 
    <span class="kwrd">ELSE</span> 
    <span class="kwrd">BEGIN</span> 
        <span class="kwrd">PRINT</span> <span class="str">'Job '</span><span class="str">''</span> + @JOB_NAME + <span class="str">''</span><span class="str">' already started '</span>; 
    <span class="kwrd">END</span> 
<span class="kwrd">END</span>

It is an extremely straight forward procedure.  It looks to see if there are any instances of job that are still running and if it does not find one it starts one.  The addition of some very basic print statements helped when going through logs.

Now all we had to do was insert a call to this stored procedure at the end of the user interaction and our conflicting job executions went away.  I added a little code at the end of the primary SSIS package to check for additional data from the user that was submitted since the last start and re-started the lengthy ETL process.  This way the job was not set on some polling schedule where it would run when it was not needed, and the users had to wait the minimum amount of time for their data to process.

Possible Enhancements

As you can see this is a very basic implementation which does exactly what I needed.  There are a couple things you could add to make this more flexible and portable:

  1. Adding a parameter for the SQL Agent Job Name instead of having it hard coded
  2. Returning some sort of status code so the calling application knows the state of the job

Final Thoughts

Again my implementation was very simple but It is still a handy little utility that I will be holding onto for future data projects.  It took a little forethought to design the primary package and data model to allow users to submit data that would wait in queue if another user already started the job but it was well worth the effort.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

JJ Bussert
Architect
United States United States
No Biography provided
Follow on   Twitter

Comments and Discussions

 
-- There are no messages in this forum --
| Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 12 Nov 2013
Article Copyright 2013 by JJ Bussert
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid