I’m currently working on a big .NET Core application that is using SQL Server jobs to perform some background tasks.

Today, I encountered an issue when I tried to start the job two times:

SQLServerAgent Error: Request to run job XXX (from User YYYY) refused because the job already has a pending request from User
YYYY.

Indeed, when the job is started, it can takes some times before finishing. To prevent that, we need to check if the job has not been already requested AND if it’s not currently running. I’ve found a lot of SQL scripts which should do the job but no one worked for me so here is my attempt to provide you that kind of script:

SELECT @CountJobs=COUNT(*)   
    FROM
        msdb.dbo.sysjobs_view job
        INNER JOIN msdb.dbo.sysjobactivity activity
        ON (job.job_id = activity.job_id)
        WHERE name='JobName'
        AND run_requested_date IS NOT NULL 
		AND start_execution_date IS NOT NULL
		AND stop_execution_date IS NULL

IF @CountJobs=0 EXEC msdb..sp_start_job @job_name='JobName'

First, we check if there is a job named as the one we want to start and we check if the job:

  • Has not been requested
  • Has not been started
  • Is not ended

With this version, I’ve been able to manage my issue and prevent my job to run 2 times (and raising an error the second time).

Happy coding!


Using Azure Blob Storage to host static files Create your own Google Home / Alexa with Cognitive Services Speech SDK and LUIS

Leave a Reply

Your email address will not be published. Required fields are marked *