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
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).