how to know status of currently running jobs

Question

I need to know if a given Job is currently running on Ms SQL 2008 server  So as to not to invoke same job again that may lead to concurrency issues

User · Answer

Given a job  I assume you know its name  you can use   EXEC msdb dbo sp help job  Job name    Your Job Name    as suggested in MSDN Job Help Procedure  It returns a lot of informations about the job  owner  server  status and so on

User · Answer

We ve found and have been using this code for a good solution  This code will start a job  and monitor it  killing the job automatically if it exceeds a time limit                                                                     --This SQL will take a list of SQL Agent jobs  names must match   --start them so they re all running together  and then --monitor them  not quitting until all jobs have completed  -- --In essence  it s an SQL  watchdog  loop to start and monitor SQL Agent Jobs -- --Code from http   cc davelozinski com code sql-watchdog-loop-start-monitor-sql-agent-jobs --                                                                   SET NOCOUNT ON   -------- BEGIN ITEMS THAT NEED TO BE CONFIGURED --------  --The amount of time to wait before checking again  --to see if the jobs are still running  --Should be in hh mm ss format   DECLARE  WaitDelay VARCHAR 8     00 00 20   --Job timeout  Eg  if the jobs are running longer than this  kill them  DECLARE  TimeoutMinutes INT   240  DECLARE  JobsToRunTable TABLE       JobName NVARCHAR 128  NOT NULL      JobID UNIQUEIDENTIFIER NULL      Running INT NULL    --Insert the names of the SQL jobs here  Last two values should always be NULL at this point  --Names need to match exactly  so best to copy paste from the SQL Server Agent job name  INSERT INTO  JobsToRunTable  JobName  JobID  Running  VALUES   NameOfFirstSQLAgentJobToRun  NULL NULL  INSERT INTO  JobsToRunTable  JobName  JobID  Running  VALUES   NameOfSecondSQLAgentJobToRun  NULL NULL  INSERT INTO  JobsToRunTable  JobName  JobID  Running  VALUES   NameOfXSQLAgentJobToRun  NULL NULL   -------- NOTHING FROM HERE DOWN SHOULD NEED TO BE CONFIGURED --------  DECLARE  ExecutionStatusTable TABLE       JobID UNIQUEIDENTIFIER PRIMARY KEY  -- Job ID which will be a guid     LastRunDate INT  LastRunTime INT  -- Last run date and time     NextRunDate INT  NextRunTime INT  -- Next run date and time     NextRunScheduleID INT  -- an internal schedule id     RequestedToRun INT  RequestSource INT  RequestSourceID VARCHAR 128       Running INT     -- 0 or 1  1 means the job is executing     CurrentStep INT  -- which step is running     CurrentRetryAttempt INT  -- retry attempt     JobState INT -- 0   Not idle or suspended  1   Executing  2   Waiting For Thread                       -- 3   Between Retries  4   Idle  5   Suspended                        -- 6   WaitingForStepToFinish  7   PerformingCompletionActions    DECLARE  JobNameToRun NVARCHAR 128    NULL DECLARE  IsJobRunning BIT   1 DECLARE  AreJobsRunning BIT   1 DECLARE  job owner sysname   SUSER SNAME   DECLARE  JobID UNIQUEIDENTIFIER   null DECLARE  StartDateTime DATETIME   GETDATE   DECLARE  CurrentDateTime DATETIME   null DECLARE  ExecutionStatus INT   0 DECLARE  MaxTimeExceeded BIT   0  --Loop through and start every job DECLARE dbCursor CURSOR FOR SELECT JobName FROM  JobsToRunTable OPEN dbCursor FETCH NEXT FROM dbCursor INTO  JobNameToRun WHILE   FETCH STATUS   0 BEGIN     EXEC  msdb   dbo  sp start job  JobNameToRun     FETCH NEXT FROM dbCursor INTO  JobNameToRun END CLOSE dbCursor DEALLOCATE dbCursor  print                                                                     print  Jobs started      CAST  StartDateTime as varchar  print                                                                      --Debug  if needed  --SELECT   FROM  JobsToRunTable  WHILE 1 1 AND  AreJobsRunning   1 BEGIN      --This has to be first with the delay to make sure the jobs     --have time to actually start up and are recognized as  running      WAITFOR DELAY  WaitDelay       --Reset for each loop iteration     SET  AreJobsRunning   0      --Get the currently executing jobs by our user name     INSERT INTO  ExecutionStatusTable     EXECUTE  master   dbo  xp sqlagent enum jobs 1   job owner      --Debug  if needed      --SELECT  ExecutionStatusTable     FROM  ExecutionStatusTable      --select every job to see if it s running     DECLARE dbCursor CURSOR FOR          SELECT x  Running   x  JobID   sj name          FROM  ExecutionStatusTable x          INNER JOIN  msdb   dbo  sysjobs sj ON sj job id   x JobID         INNER JOIN  JobsToRunTable jtr on sj name   jtr JobName     OPEN dbCursor FETCH NEXT FROM dbCursor INTO  IsJobRunning   JobID   JobNameToRun      --Debug  if needed      --SELECT x  Running   x  JobID   sj name      --  FROM  ExecutionStatusTable x      --  INNER JOIN msdb dbo sysjobs sj ON sj job id   x JobID     --  INNER JOIN  JobsToRunTable jtr on sj name   jtr JobName      WHILE   FETCH STATUS   0     BEGIN         --bitwise operation to see if the loop should continue         SET  AreJobsRunning    AreJobsRunning    IsJobRunning          UPDATE  JobsToRunTable         SET Running    IsJobRunning  JobID    JobID         WHERE JobName    JobNameToRun          --Debug  if needed          --SELECT  JobsToRun     FROM  JobsToRunTable          SET  CurrentDateTime GETDATE            IF  IsJobRunning   1         BEGIN -- Job is running or finishing  not idle               IF DATEDIFF mi   StartDateTime   CurrentDateTime   gt   TimeoutMinutes             BEGIN                      print                                                                                     print  JobNameToRun     exceeded timeout limit of      TimeoutMinutes     minutes  Stopping                   --Stop the job                 EXEC  msdb   dbo  sp stop job  job name    JobNameToRun             END             ELSE             BEGIN                 print  JobNameToRun     running for     CONVERT VARCHAR 25  DATEDIFF mi   StartDateTime   CurrentDateTime       minute s                END         END          IF  IsJobRunning   0          BEGIN             --Job isn t running             print                                                                                 print  JobNameToRun     completed or did not run      CAST  CurrentDateTime as VARCHAR          END          FETCH NEXT FROM dbCursor INTO  IsJobRunning   JobID   JobNameToRun      END -- WHILE   FETCH STATUS   0     CLOSE dbCursor     DEALLOCATE dbCursor      --Clear out the table for the next loop iteration     DELETE FROM  ExecutionStatusTable      print                                                                      END -- WHILE 1 1 AND  AreJobsRunning   1  SET  CurrentDateTime   GETDATE   print  Finished at     CAST  CurrentDateTime as varchar  print CONVERT VARCHAR 25  DATEDIFF mi   StartDateTime   CurrentDateTime       minutes total run time

User · Answer

It looks like you can use msdb dbo sysjobactivity  checking for a record with a non-null start execution date and a null stop execution date  meaning the job was started  but has not yet completed   This would give you currently running jobs   SELECT sj name      sja   FROM msdb dbo sysjobactivity AS sja INNER JOIN msdb dbo sysjobs AS sj ON sja job id   sj job id WHERE sja start execution date IS NOT NULL    AND sja stop execution date IS NULL

User · Answer

DECLARE  StepCount INT SELECT  StepCount   COUNT 1  FROM msdb dbo sysjobsteps WHERE job id    0523333-5C24-1526-8391-AA84749345666  --JobID   SELECT           JobName            JobStepID            JobStepName            JobStepStatus            RunDateTime            RunDuration      FROM               SELECT                  j  name  AS  JobName               Jh  step id  AS  JobStepID               jh  step name  AS  JobStepName               CASE                  WHEN jh  run status    0 THEN  Failed                  WHEN jh  run status    1 THEN  Succeeded                  WHEN jh  run status    2 THEN  Retry  step only                   WHEN jh  run status    3 THEN  Canceled                  WHEN jh  run status    4 THEN  In-progress message                  WHEN jh  run status    5 THEN  Unknown                  ELSE  N A                  END AS  JobStepStatus               msdb dbo agent datetime run date  run time  AS  RunDateTime               CAST jh  run duration  10000 AS VARCHAR           CAST jh  run duration  100 100 AS VARCHAR          CAST jh  run duration  100 AS VARCHAR  AS  RunDuration               ROW NUMBER   OVER                                PARTITION BY jh  run date                  ORDER BY jh  run date  DESC  jh  run time  DESC               AS  RowNumber          FROM              msdb  dbo   sysjobhistory  jh             INNER JOIN msdb  dbo   sysjobs  j                 ON jh  job id    j  job id          WHERE              j  name     ProcessCubes  --Job Name             AND jh  step id   gt  0             AND CAST RTRIM run date  AS DATE    CAST GETDATE   AS DATE  --Current Date       A     WHERE           RowNumber   lt    StepCount         AND  JobStepStatus     Failed

User · Answer

EXECUTE master dbo xp sqlagent enum jobs 1      Notice the column Running  obviously 1 means that it is currently running  and  Current Step   This returns job id to you  so you ll need to look these up  e g    SELECT top 100    FROM   msdb  sysjobs  WHERE  job id IN  0x9DAD1B38EB345D449EAFA5C5BFDC0E45  0xC00A0A67D109B14897DD3DFD25A50B80  0xC92C66C66E391345AE7E731BFA68C668

User · Answer

I found a better answer by Kenneth Fisher   The following query returns only currently running jobs   SELECT     ja job id      j name AS job name      ja start execution date            ISNULL last executed step id 0  1 AS current executed step id      Js step name FROM msdb dbo sysjobactivity ja  LEFT JOIN msdb dbo sysjobhistory jh ON ja job history id   jh instance id JOIN msdb dbo sysjobs j ON ja job id   j job id JOIN msdb dbo sysjobsteps js     ON ja job id   js job id     AND ISNULL ja last executed step id 0  1   js step id WHERE   ja session id         SELECT TOP 1 session id FROM msdb dbo syssessions ORDER BY agent start date DESC     AND start execution date is not null AND stop execution date is null    You can get more information about a job by adding more columns from msdb dbo sysjobactivity table in select clause

User · Answer

EXEC msdb dbo sp help job  Job name    Your Job Name    check field execution status  0 - Returns only those jobs that are not idle or suspended  1 - Executing  2 - Waiting for thread  3 - Between retries  4 - Idle  5 - Suspended  7 - Performing completion actions   If you need the result of execution  check the field last run outcome  0   Failed 1   Succeeded 3   Canceled 5   Unknown  https   msdn microsoft com en-us library ms186722 aspx

User · Answer

This query will give you the exact output for current running jobs  This will also shows the duration of running job in minutes       WITH     CTE Sysession  AgentStartDate      AS                SELECT MAX AGENT START DATE  AS AgentStartDate FROM MSDB DBO SYSSESSIONS          SELECT sjob name AS JobName          CASE              WHEN SJOB enabled   1 THEN  Enabled              WHEN sjob enabled   0 THEN  Disabled              END AS JobEnabled          sjob description AS JobDescription          CASE              WHEN ACT start execution date IS NOT NULL AND ACT stop execution date IS NULL  THEN  Running              WHEN ACT start execution date IS NOT NULL AND ACT stop execution date IS NOT NULL AND HIST run status   1 THEN  Stopped              WHEN HIST run status   0 THEN  Failed              WHEN HIST run status   3 THEN  Canceled          END AS JobActivity          DATEDIFF MINUTE act start execution date  GETDATE    DurationMin          hist run date AS JobRunDate          run DURATION 10000 AS Hours           run DURATION 10000  100 AS Minutes            run DURATION 10000  100 AS Seconds          hist run time AS JobRunTime           hist run duration AS JobRunDuration           tulsql11 dba  AS JobServer          act start execution date AS JobStartDate          act last executed step id AS JobLastExecutedStep          act last executed step date AS JobExecutedStepDate          act stop execution date AS JobStopDate          act next scheduled run date AS JobNextRunDate          sjob date created AS JobCreated          sjob date modified AS JobModified                   FROM MSDB DBO syssessions AS SYS1         INNER JOIN CTE Sysession AS SYS2 ON SYS2 AgentStartDate   SYS1 agent start date         JOIN  msdb dbo sysjobactivity act ON act session id   SYS1 session id         JOIN msdb dbo sysjobs sjob ON sjob job id   act job id         LEFT JOIN  msdb dbo sysjobhistory hist ON hist job id   act job id AND hist instance id   act job history id         WHERE ACT start execution date IS NOT NULL AND ACT stop execution date IS NULL         ORDER BY ACT start execution date DESC

User · Answer

You can query the table msdb dbo sysjobactivity to determine if the job is currently running

[sql-server] how to know status of currently running jobs

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to sql-agent