Qfyilyi

I have different triggers that kick off a job. Sometimes multiple triggers can be set off to kick this job off at the same time and my initial check to see if the job is running doesn't work properly.

DECLARE @JobCount NUMERIC

SET @JobCount = (SELECT COUNT(*)

                   FROM msdb.dbo.sysjobactivity ja

                   JOIN msdb.dbo.sysjobs j

                        ON ja.job_id = j.job_id

                  WHERE j.name = 'JobName'

                        AND ja.start_execution_date IS NOT NULL

                        AND ja.stop_execution_date IS NULL) 



IF @JobCount = 0

  BEGIN

    EXEC msdb.dbo.sp_start_job 

         'JobName'

  END

This is at the bottom of the triggers but if the triggers were initiated at the same time, it still errors out with Error: Request to run job JobName refused because the job is already running from a request by User. BEGIN TRY/CATCH doesn't work here. I know in Oracle you could have EXCEPTION WHEN OTHERS IS NULL - is there anything I can do here to just ignore the error if it happens? I understand the job is still running, I just don't want to produce an error.

Thanks

EDIT: Hack workaround is adding WAITFOR DELAY '00:00:01' but also open to other suggestions

By having multiple triggers calling the job, you've introduced a race condition, so the same triggers causing the race, are not likely to resolve it. You need an outside observer.

You might try...

Create a new table, such as:

CREATE TABLE JobControl

(

     Id INT IDENTITY(1,1) PRIMARY KEY,

     DateCreated DATETIME DEFAULT CURRENT_TIMESTAMP

)

Alter your triggers, so that they insert a row into this table rather than call the job.

Create a 2nd job that runs every n seconds (or loops constantly if you desire). That job checks for rows in the JobControl table. If rows are found (by deleting them) it runs the main job (if its not already running). If it deleted rows from this table, and it turns out the job is already running, it aborts and puts the rows back for next time.

Such as:

SET NOCOUNT ON



DECLARE @MYID UNIQUEIDENTIFIER, @rowsDeleted INT

SELECT @MYID=JOB_ID FROM MSDB.DBO.SYSJOBS WHERE NAME='JobName'



CREATE TABLE #enum_job

(

  Job_ID UNIQUEIDENTIFIER,

  Last_Run_Date INT,

  Last_Run_Time INT,

  Next_Run_Date INT,

  Next_Run_Time INT,

  Next_Run_Schedule_ID INT,

  Requested_To_Run INT,

  Request_Source INT,

  Request_Source_ID VARCHAR(100),

  Running INT,

  Current_Step INT,

  Current_Retry_Attempt INT,

  State INT

)



IF @MYID IS NOT NULL

BEGIN

    BEGIN TRAN

        -- delete all rows in table, capture row count

        DELETE J

        FROM JobControl J

        SET @rowsDeleted=@@ROWCOUNT



        -- check running jobs

        INSERT INTO #enum_job

        EXEC master.dbo.xp_sqlagent_enum_jobs 1, NULL



        -- if the job isn't running, and we had rows to delete, run the job

        IF EXISTS (SELECT 1 FROM #enum_job WHERE Job_ID=@MYID AND Running=0) AND @rowsDeleted>0

        BEGIN

            EXEC msdb.dbo.sp_start_job 'JobName'

        END



        -- if the job is already running, and we had rows to delete, roll back

        IF EXISTS (SELECT 1 FROM #enum_job WHERE Job_ID=@MYID AND Running=1) AND @rowsDeleted>0

        BEGIN

            ROLLBACK TRAN

        END

    -- if the transaction is still open, commit it

    IF @@TRANCOUNT>0 COMMIT TRAN

END