Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

StageStoreJob

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.

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO
share|improve this question
    
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

1 Answer 1

up vote 1 down vote accepted

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 sproc that restarts this loop. The solution to this is my original workaround.

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

BEGIN
WAITFOR DELAY '00:01';
    EXECUTE dbo.LoopStageStore
END; GO

Just make it wait a little bit.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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