So have an ETL job that runs through integration services that I start with a SQL job name StartStageStore. The last step of this job is to start another job and report success. The job this calls to is referred to as LoopStageStore. All LoopStageStore does is execute a stored procedure that Restarts the StartStageStore job all over again this way we are constantly pulling source data into our system. This job has been running for sever weeks now without failure. Until the last two nights I started getting this error "Executed as user: AgentUser. SQLServerAgent Error: Request to run job Stage/Store AgentUser refused because the job is already running from a request by User AgentUser. [SQLSTATE 42000] (Error 22022). The step failed."

This should not be possible as the run StartStageStore job finishes and is no longer running when LoopStageStore has started.


Here we can see the last step executed at 12:19:14.LoopStageStore

And the Next Job Fails at 12:19:15 Saying that StartStageStore is currently running. I already have a workaround for this where I just throw a Delay on the Second job. I'm just wondering if anyone knows why this would happen.

    EXECUTE dbo.LoopStageStore
  • Is sysjobhistory large? It's possible that the first job takes a significant amount of time to update the job history. – Aaron Bertrand Oct 23 '12 at 15:02
  • It is not. We only keep a weeks worth of job log history. However that is my assumption as to what is happening. The part that confuses me is this issue only pops up once a day at the most. The job runs a solid 20 + times a day. – Zane Oct 23 '12 at 16:07
  • A SQL Server system goes through many phases of activity during a day. Have you looked at tracking locking and blocking in msdb throughout the day and seeing if you discover any correlation? – Aaron Bertrand Oct 23 '12 at 16:10
  • The problem appears to be that one certain occasions it takes longer that 1 second to finish up that write log to the Jobs Finished Table (Starcraft Reference I know that's not the actual name). So the wait time I have placed on there should fix the problem. – Zane Oct 23 '12 at 16:17

Alright so the problem I was having was that the write time to the log file for job completion occasionally takes longer than it takes to run the stored procedure that restarts this loop.

The solution to this is my original workaround.

In case anyone wants to use it here is the code.

    EXECUTE dbo.LoopStageStore

Just make it wait a little bit.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.