Tuesday, 23 February 2016

How to check if a SQL Server Agent job is running

I was getting an error that "SQLServerAgent Error: Request to run job <Job name>  from User <User name>  refused because the job already has a pending request from User <other User name>.

To avoid the above error, it is always required to check the running status of a SQL job, if it is already running or not. It also might be the scenario that it is already have a pending  request to execute by some other user. So below script will help to check the status of any SQL job.

Below T-SQL script is an If condition which is identifying the job status by the SQL job name. This If condition will be true if current job is not running.

For returning the value of the same job you need to make an inner join between msdb.dbo.sysjobs_view and msdb.dbo.sysjobactivity 
IF NOT EXISTS(  
        select 1
        from msdb.dbo.sysjobs_view job
        inner join msdb.dbo.sysjobactivity activity on job.job_id = activity.job_id
        where
            activity.run_Requested_date is not null
        and activity.stop_execution_date is null
        and job.name = <SQL Job name> in single quotes
        )
BEGIN    
    EXEC msdb.dbo.sp_start_job <SQL Job name> in single quotes;
END

I wish above script would help you to find the running status of any SQL job.

No comments:

Post a Comment