SQL Agent Job can only have one instance running at a time

A job in SQL Agent server can only have one instance running by default, that is, if you have a running Job in your SQL agent called "Test Job", you will get an error if you start the test job now. Because SQL Server specifies that under a single SQL Server account, it cannot start the same job two times at the same time, the job can be started again only after the previous boot execution has completed.

Then the question is, how do you use the SQL statement to detect if the job has been executed? Here is a statement that can be used as a reference:

 declare   @Job_ID  as  uniqueidentifier  select  Span style= "color: #008000;" > @Job_ID  =  job_id from  Msdb.dbo.sysjobs where  name  '  james_test   '   exec  master. Sp_msget_jobstate  @Job_ID  --  The return value is 1-running, 4-Indicates completion (success or failure)  

The key to the above statement is to call a system stored procedure in the Master library Sp_msget_jobstate, the structure of this stored procedure can be found from SQL Server as follows, if necessary, you can establish a custom stored procedure to modify:

 Use [Master]GO/** * * * object:storedprocedure [SYS].    [Sp_msget_jobstate] Script DATE:2016/1/29 13:51:37 * * * * **/SETAnsi_nulls onGOSETQuoted_identifier onGO--Procedure sp_msget_jobstate----Descriptions:--The proc takes a specific job ID and returns the job state of the job--Returns a row with one column job_state--Returns a row with NULL if job does not exist-- --Parameters:as defined in create statement----returns:0-Success--1-otherwise----Security:--Requires Certificate Signature for catalog access--ALTER procedure [SYS].[sp_msget_jobstate]    @job_id             uniqueidentifier asBEGIN    DECLARE @is_sysadmin INT    DECLARE @job_ownersysnameDECLARE @job_state   INT     DECLARE @job_id_as_char VARCHAR( $)    SETNOCOUNT on    CREATE TABLE#xp_results (job_iduniqueidentifier  not NULL, Last_run_dateINT               not NULL, Last_run_timeINT               not NULL, Next_run_dateINT               not NULL, Next_run_timeINT               not NULL, next_run_schedule_idINT               not NULL, Requested_to_runINT               not NULL,--BOOLRequest_sourceINT               not NULL, request_source_id sysname collate database_defaultNULL, runningINT               not NULL,--BOOLCurrent_stepINT               not NULL, Current_retry_attemptINT               not NULL, Job_stateINT               not NULL)    --need a job_id    if(@job_id  is NULL)    BEGIN        SELECT @job_id_as_char = CONVERT(VARCHAR( $),@job_id)        RAISERROR(14262,-1,-1,'@job_id',@job_id_as_char)        RETURN(1)--Failure    END    --Capture Job execution information (for local jobs only since that's all SQLServerAgent caches)    SELECT @is_sysadmin = ISNULL(Is_srvrolemember(N'sysadmin'),0)    SELECT @job_owner = suser_sname(Suser_sid())    INSERT  into#xp_resultsEXECUTEMaster.dbo.xp_sqlagent_enum_jobs@is_sysadmin,@job_owner    --Select The job state of the job in question    SELECT @job_state =Job_state from#xp_resultsWHERE @job_id =job_id--Error If we have no rows selected    if(@job_state  is NULL)    BEGIN        SELECT @job_id_as_char = CONVERT(VARCHAR( $),@job_id)        RAISERROR(14262,-1,-1,'@job_id',@job_id_as_char)        RETURN(1)--Failure    END    ELSE        SELECT @job_state        -- All done    DROP TABLE#xp_resultsRETURN(0)--SuccessEND

If the query to the job in the SQL Agent is executing, you can use the stored procedure msdb.dbo.sp_stop_job to force stop the job, and then use the stored procedure msdb.dbo.sp_start_job to start the job. It is important to note that if the msdb.dbo.sp_stop_job is raised on a stopped job or if the msdb.dbo.sp_start_job is raised on the job being performed, the stored procedure is called Msdb.dbo.sp_start_ Job and Msdb.dbo.sp_stop_job need to determine the current execution state of the job, or use the SQL Try catch statement to catch errors, Msdb.dbo.sp_stop_job and Msdb.dbo.sp_stop_ The job is used as follows:

EXEC @job_name = ' job_name ' -- Job_name is the Job's name. EXEC msdb.dbo.sp_stop_job    @job_name='job_name'  --job_name is the Job's name.

SQL Agent Job can only have one instance running at a time