How can I determine the status of a job

Question

I have a Stored procedure which schedules a job  This Job takes a lot of time to get completed  approx 30 to 40 min   I need to get to know the status of this Job  Below details would help me  1  How to see the list of all jobs that have got scheduled for a future time and are yet to start  2  How to see the the list of jobs running and the time span from when they are running  3  How to see if the job has completed successfully or has stoped in between because of any error

User · Answer

This is an old question  but I just had a similar situation where I needed to check on the status of jobs on SQL Server  A lot of people mentioned the sysjobactivity table and pointed to the MSDN documentation which is great  However  I d also like to highlight the Job Activity Monitor which provides the status on all jobs that are defined on your server

User · Answer

I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives   See this for reference  What does it mean to have jobs with a null stop date   You can also validate this by analyzing the sp help jobactivity procedure in msdb   I realize that this is an old message on SO  but I found this message only partially helpful because of the problem    SELECT     job name       job job id       job originating server       activity run requested date       DATEDIFF  SECOND  activity run requested date  GETDATE     as Elapsed FROM      msdb dbo sysjobs view job JOIN     msdb dbo sysjobactivity activity ON      job job id   activity job id JOIN     msdb dbo syssessions sess ON     sess session id   activity session id JOIN       SELECT         MAX  agent start date   AS max agent start date     FROM         msdb dbo syssessions   sess max ON     sess agent start date   sess max max agent start date WHERE      run requested date IS NOT NULL AND stop execution date IS NULL

User · Answer

I ran into issues on one of my servers querying MSDB tables  aka code listed above  as one of my jobs would come up running  but it was not   There is a system stored procedure that returns the execution status  but one cannot do a insert exec statement without an error   Inside that is another system stored procedure that can be used with an insert exec statement   INSERT INTO  Job EXEC master dbo xp sqlagent enum jobs 1 dbo   And the table to load it into   CREATE TABLE  Job                  job id               UNIQUEIDENTIFIER NOT NULL                   last run date         INT              NOT NULL                   last run time         INT              NOT NULL                   next run date         INT              NOT NULL                   next run time         INT              NOT NULL                   next run schedule id  INT              NOT NULL                   requested to run      INT              NOT NULL  -- BOOL                  request source        INT              NOT NULL                   request source id     sysname          COLLATE database default NULL                   running               INT              NOT NULL  -- BOOL                  current step          INT              NOT NULL                   current retry attempt INT              NOT NULL                   job state             INT              NOT NULL

User · Answer

The tasks above work but I have seen many records in the msdb dbo sysjobactivity where run Requested date is not null  and stop execution date is null  ---- and the job is not currently running    I would recommend running the following script to clear out all of the bogus entries  make sure no jobs are running at the time    SQL2008       delete activity     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

User · Answer

we can query the msdb in many ways to get the details   few are  select job Name  job job ID  job Originating Server activity run requested Date  datediff minute  activity run requested Date  getdate    as Elapsed  from msdb dbo sysjobs view job  inner join msdb dbo sysjobactivity activity on  job job id   activity job id   where run Requested date is not null  and stop execution date is null  and job name like  Your Job Prefix

User · Answer

You could try using the system stored procedure sp help job  This returns information on the job  its steps  schedules and servers  For example  EXEC msdb dbo sp help job  Job name    Your Job Name    SQL Books Online should contain lots of information about the records it returns   For returning information on multiple jobs  you could try querying the following system tables which hold the various bits of information on the job   msdb dbo SysJobs msdb dbo SysJobSteps msdb dbo SysJobSchedules  msdb dbo SysJobServers  msdb dbo SysJobHistory   Their names are fairly self-explanatory  apart from SysJobServers which hold information on when the job last run and the outcome    Again  information on the fields can be found at MSDN  For example  check out the page for SysJobs

User · Answer

You haven t specified how would you like to see these details   For the first sight I would suggest to check Server Management Studio   You can see the jobs and current statuses in the SQL Server Agent part  under Jobs  If you pick a job  the Property page shows a link to the Job History  where you can see the start and end time  if there any errors  which step caused the error  and so on   You can specify alerts and notifications to email you or to page you when the job finished successfully or failed   There is a Job Activity Monitor  but actually I never used it  You can have a try   If you want to check it via T-SQL  then I don t know how you can do that

User · Answer

You haven t specified how would you like to see these details   For the first sight I would suggest to check Server Management Studio   You can see the jobs and current statuses in the SQL Server Agent part  under Jobs  If you pick a job  the Property page shows a link to the Job History  where you can see the start and end time  if there any errors  which step caused the error  and so on   You can specify alerts and notifications to email you or to page you when the job finished successfully or failed   There is a Job Activity Monitor  but actually I never used it  You can have a try   If you want to check it via T-SQL  then I don t know how you can do that

User · Answer

The most simple way I found was to create a stored procedure  Enter the  JobName  and hit go     -----------------------------------------------------------------------------------------------------------      Document Title  usp getJobStatus      Purpose         Finds a Current Jobs Run Status          Input Example   EXECUTE usp getJobStatus  MyJobName   -------------------------------------------------------------------------------------------------------------        IF OBJECT ID    usp getJobStatus   P    IS NOT NULL     DROP PROCEDURE  usp getJobStatus       GO          CREATE PROCEDURE  usp getJobStatus                             JobName NVARCHAR  1000       AS          IF OBJECT ID  TempDB   JobResults   U   IS NOT NULL DROP TABLE  JobResults         CREATE TABLE  JobResults   Job ID   UNIQUEIDENTIFIER NOT NULL                                      Last Run Date         INT NOT NULL                                      Last Run Time         INT NOT NULL                                      Next Run date         INT NOT NULL                                      Next Run Time         INT NOT NULL                                      Next Run Schedule ID  INT NOT NULL                                      Requested to Run      INT NOT NULL                                     Request Source        INT NOT NULL                                      Request Source id     SYSNAME                                     COLLATE Database Default      NULL                                      Running               INT NOT NULL                                     Current Step          INT NOT NULL                                      Current Retry Attempt INT NOT NULL                                      Job State             INT NOT NULL             INSERT   JobResults          EXECUTE master dbo xp sqlagent enum jobs 1               SELECT  job name                                                AS  Job Name                    SELECT  MAX CAST  STUFF STUFF CAST jh run date AS VARCHAR  7 0  -   5 0  -                                    STUFF STUFF REPLACE STR jh run time 6 0       0   5 0      3 0      AS DATETIME                   FROM    msdb dbo sysjobs AS j                      INNER JOIN msdb dbo sysjobhistory AS jh                         ON jh job id   j job id AND jh step id   0                  WHERE j  name  LIKE        JobName                        GROUP BY j  name                                        AS  Last Completed DateTime                    SELECT  TOP 1 start execution date                  FROM    msdb dbo sysjobactivity                 WHERE   job id   r job id                 ORDER BY start execution date DESC                      AS  Job Start DateTime               CASE                  WHEN r running   0 THEN                     CASE                          WHEN jobInfo lASt run outcome   0 THEN  Failed                          WHEN jobInfo lASt run outcome   1 THEN  Success                          WHEN jobInfo lASt run outcome   3 THEN  Canceled                          ELSE  Unknown                      END                         WHEN r job state   0 THEN  Success                          WHEN r job state   4 THEN  Success                          WHEN r job state   5 THEN  Success                          WHEN r job state   1 THEN  In Progress                          WHEN r job state   2 THEN  In Progress                          WHEN r job state   3 THEN  In Progress                          WHEN r job state   7 THEN  In Progress                       ELSE  Unknown  END                                 AS  Run Status Description          FROM     JobResults AS r              LEFT OUTER JOIN msdb dbo sysjobservers AS jobInfo                 ON r job id   jobInfo job id              INNER JOIN msdb dbo sysjobs AS job                 ON r job id   job job id          WHERE   job  enabled    1                 AND job name LIKE        JobName

User · Answer

This is what I m using to get the running jobs  principally so I can kill the ones which have probably hung    SELECT     job Name  job job ID      job Originating Server      activity run requested Date      datediff minute  activity run requested Date  getdate    AS Elapsed FROM     msdb dbo sysjobs view job          INNER JOIN msdb dbo sysjobactivity activity         ON  job job id   activity job id  WHERE     run Requested date is not null      AND stop execution date is null     AND job name like  Your Job Prefix     As Tim said  the MSDN   BOL documentation is reasonably good on the contents of the sysjobsX tables  Just remember they are tables in MSDB

User · Answer

WITH CTE JobStatus AS       SELECT DISTINCT NAME AS  JobName           s step id          s step name          CASE              WHEN  Enabled    1                 THEN  Enabled              ELSE  Disabled              END  JobStatus           CASE              WHEN SJH run status   0                 THEN  Failed              WHEN SJH run status   1                 THEN  Succeeded              WHEN SJH run status   2                 THEN  Retry              WHEN SJH run status   3                 THEN  Cancelled              WHEN SJH run status   4                 THEN  In Progress              ELSE  Unknown              END  JobOutcome           CONVERT VARCHAR 8   sjh run date   RunDate           CONVERT VARCHAR 8   STUFF STUFF CONVERT TIMESTAMP  RIGHT  000000    CONVERT VARCHAR 6   sjh run time   6    3  0        6  0        RunTime          RANK   OVER               PARTITION BY s step name ORDER BY sjh run date DESC                  sjh run time DESC               AS rn          SJH run status     FROM msdb  SYSJobs sj     INNER JOIN msdb  SYSJobHistory sjh ON sj job id   sjh job id     INNER JOIN msdb dbo sysjobsteps s ON sjh job id   s job id         AND sjh step id   s step id     WHERE  sj NAME LIKE  JOB NAME           AND sjh run date   CONVERT CHAR  getdate    112        SELECT   FROM CTE JobStatus WHERE rn   1     AND run status NOT IN  1 4

User · Answer

The tasks above work but I have seen many records in the msdb dbo sysjobactivity where run Requested date is not null  and stop execution date is null  ---- and the job is not currently running    I would recommend running the following script to clear out all of the bogus entries  make sure no jobs are running at the time    SQL2008       delete activity     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

User · Answer

You could try using the system stored procedure sp help job  This returns information on the job  its steps  schedules and servers  For example  EXEC msdb dbo sp help job  Job name    Your Job Name    SQL Books Online should contain lots of information about the records it returns   For returning information on multiple jobs  you could try querying the following system tables which hold the various bits of information on the job   msdb dbo SysJobs msdb dbo SysJobSteps msdb dbo SysJobSchedules  msdb dbo SysJobServers  msdb dbo SysJobHistory   Their names are fairly self-explanatory  apart from SysJobServers which hold information on when the job last run and the outcome    Again  information on the fields can be found at MSDN  For example  check out the page for SysJobs

User · Answer

This is what I m using to get the running jobs  principally so I can kill the ones which have probably hung    SELECT     job Name  job job ID      job Originating Server      activity run requested Date      datediff minute  activity run requested Date  getdate    AS Elapsed FROM     msdb dbo sysjobs view job          INNER JOIN msdb dbo sysjobactivity activity         ON  job job id   activity job id  WHERE     run Requested date is not null      AND stop execution date is null     AND job name like  Your Job Prefix     As Tim said  the MSDN   BOL documentation is reasonably good on the contents of the sysjobsX tables  Just remember they are tables in MSDB

User · Answer

You could try using the system stored procedure sp help job  This returns information on the job  its steps  schedules and servers  For example  EXEC msdb dbo sp help job  Job name    Your Job Name    SQL Books Online should contain lots of information about the records it returns   For returning information on multiple jobs  you could try querying the following system tables which hold the various bits of information on the job   msdb dbo SysJobs msdb dbo SysJobSteps msdb dbo SysJobSchedules  msdb dbo SysJobServers  msdb dbo SysJobHistory   Their names are fairly self-explanatory  apart from SysJobServers which hold information on when the job last run and the outcome    Again  information on the fields can be found at MSDN  For example  check out the page for SysJobs

User · Answer

Below script gets job status for every job on the server  It also tells how many steps are there and what is the currently running step and elasped time   SELECT sj Name      CASE         WHEN sja start execution date IS NULL THEN  Never ran          WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL THEN  Running          WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NOT NULL THEN  Not running      END AS  RunStatus       CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then js StepCount else null end As TotalNumberOfSteps      CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then ISNULL sja last executed step id 1 js StepCount  else null end as currentlyExecutingStep      CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then datediff minute  sja run requested date  getdate    ELSE NULL end as ElapsedTime FROM msdb dbo sysjobs sj JOIN msdb dbo sysjobactivity sja ON sj job id   sja job id CROSS APPLY  SELECT COUNT    FROM msdb dbo sysjobsteps as js WHERE js job id   sj job id  as js StepCount  WHERE session id         SELECT MAX session id  FROM msdb dbo sysjobactivity  ORDER BY RunStatus desc

User · Answer

SELECT sj name   FROM msdb  sysjobactivity aj   JOIN msdb  sysjobs sj     on sj job id   aj job id  WHERE aj stop execution date  IS NULL     -- job hasn t stopped running    AND aj start execution date IS NOT NULL -- job is currently running    AND sj name     lt your Job Name gt      AND NOT EXISTS  -- make sure this is the most recent run                    select 1                      from msdb  sysjobactivity new                     where new job id   aj job id                       and new start execution date  gt  aj start execution date     print  running

User · Answer

This is an old question  but I just had a similar situation where I needed to check on the status of jobs on SQL Server  A lot of people mentioned the sysjobactivity table and pointed to the MSDN documentation which is great  However  I d also like to highlight the Job Activity Monitor which provides the status on all jobs that are defined on your server

User · Answer

This will show last run status time or if running  it shows current run time  step number info  and SPID  if it has associated SPID   It also shows enabled disabled and job user where it converts to NT SID format for unresolved user accounts    CREATE TABLE  list running SQL jobs       job id UNIQUEIDENTIFIER NOT NULL     last run date INT NOT NULL     last run time INT NOT NULL     next run date INT NOT NULL     next run time INT NOT NULL     next run schedule id INT NOT NULL     requested to run INT NOT NULL     request source INT NOT NULL     request source id sysname NULL     running INT NOT NULL     current step INT NOT NULL     current retry attempt INT NOT NULL     job state INT NOT NULL     DECLARE  sqluser NVARCHAR 128           is sysadmin INT   SELECT  is sysadmin   ISNULL IS SRVROLEMEMBER N sysadmin    0    DECLARE read sysjobs for running CURSOR FOR     SELECT DISTINCT SUSER SNAME owner sid FROM msdb dbo sysjobs  OPEN read sysjobs for running  FETCH NEXT FROM read sysjobs for running INTO  sqluser   WHILE   FETCH STATUS   0 BEGIN     INSERT INTO  list running SQL jobs     EXECUTE master dbo xp sqlagent enum jobs  is sysadmin   sqluser      FETCH NEXT FROM read sysjobs for running     INTO  sqluser  END   CLOSE read sysjobs for running  DEALLOCATE read sysjobs for running   SELECT j name         Enbld    CASE j enabled                      WHEN 0                          THEN  no                       ELSE  YES                   END          Min    DATEDIFF MINUTE  a start execution date  ISNULL a stop execution date  GETDATE             Status    CASE                       WHEN a start execution date IS NOT NULL                           AND a stop execution date IS NULL                           THEN  Executing                        WHEN h run status   0                           THEN  FAILED                        WHEN h run status   2                           THEN  Retry                        WHEN h run status   3                           THEN  Canceled                        WHEN h run status   4                           THEN  InProg                        WHEN h run status   1                           THEN  Success                        ELSE  Idle                    END        r current step        spid   p session id        owner   ISNULL SUSER SNAME j owner sid    S-    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1    - CONVERT BIGINT  256    CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256      -    CONVERT NVARCHAR 12   UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   4   1     256   CONVERT BIGINT  NULLIF UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256  0   - CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256     ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   5   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   6   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 6   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   7   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   8   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 8   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   9   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   10   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 10   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   11   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   12   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 12   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   13   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   14   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 14   -1     0        --SHOW as NT SID when unresolved        a start execution date        a stop execution date        t subsystem        t step name FROM msdb dbo sysjobs j     LEFT OUTER JOIN  SELECT DISTINCT   FROM  list running SQL jobs  r         ON j job id   r job id     LEFT OUTER JOIN msdb dbo sysjobactivity a         ON j job id   a job id             AND a start execution date IS NOT NULL             --AND a stop execution date IS NULL             AND NOT EXISTS                               SELECT                   FROM msdb dbo sysjobactivity at                 WHERE at job id   a job id                     AND at start execution date  gt  a start execution date                   LEFT OUTER JOIN sys dm exec sessions p         ON p program name LIKE  SQLAgent 0x               AND j job id   SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   7  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   5  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   3  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   1  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   11  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   9  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   15  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   13  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   17  4     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   21  12      LEFT OUTER JOIN msdb dbo sysjobhistory h         ON j job id   h job id             AND h instance id   a job history id     LEFT OUTER JOIN msdb dbo sysjobsteps t         ON t job id   j job id             AND t step id   r current step ORDER BY 1   DROP TABLE  list running SQL jobs

User · Answer

I would like to point out that none of the T-SQL on this page will work precisely because none of them join to the syssessions table to get only the current session and therefore could include false positives   See this for reference  What does it mean to have jobs with a null stop date   You can also validate this by analyzing the sp help jobactivity procedure in msdb   I realize that this is an old message on SO  but I found this message only partially helpful because of the problem    SELECT     job name       job job id       job originating server       activity run requested date       DATEDIFF  SECOND  activity run requested date  GETDATE     as Elapsed FROM      msdb dbo sysjobs view job JOIN     msdb dbo sysjobactivity activity ON      job job id   activity job id JOIN     msdb dbo syssessions sess ON     sess session id   activity session id JOIN       SELECT         MAX  agent start date   AS max agent start date     FROM         msdb dbo syssessions   sess max ON     sess agent start date   sess max max agent start date WHERE      run requested date IS NOT NULL AND stop execution date IS NULL

User · Answer

You haven t specified how would you like to see these details   For the first sight I would suggest to check Server Management Studio   You can see the jobs and current statuses in the SQL Server Agent part  under Jobs  If you pick a job  the Property page shows a link to the Job History  where you can see the start and end time  if there any errors  which step caused the error  and so on   You can specify alerts and notifications to email you or to page you when the job finished successfully or failed   There is a Job Activity Monitor  but actually I never used it  You can have a try   If you want to check it via T-SQL  then I don t know how you can do that

User · Answer

-- Microsoft SQL Server 2008 Standard Edition  IF EXISTS SELECT 1            FROM msdb dbo sysjobs J            JOIN msdb dbo sysjobactivity A                ON A job id J job id            WHERE J name N Your Job Name             AND A run requested date IS NOT NULL            AND A stop execution date IS NULL                PRINT  The job is running   ELSE     PRINT  The job is not running

User · Answer

WITH CTE JobStatus AS       SELECT DISTINCT NAME AS  JobName           s step id          s step name          CASE              WHEN  Enabled    1                 THEN  Enabled              ELSE  Disabled              END  JobStatus           CASE              WHEN SJH run status   0                 THEN  Failed              WHEN SJH run status   1                 THEN  Succeeded              WHEN SJH run status   2                 THEN  Retry              WHEN SJH run status   3                 THEN  Cancelled              WHEN SJH run status   4                 THEN  In Progress              ELSE  Unknown              END  JobOutcome           CONVERT VARCHAR 8   sjh run date   RunDate           CONVERT VARCHAR 8   STUFF STUFF CONVERT TIMESTAMP  RIGHT  000000    CONVERT VARCHAR 6   sjh run time   6    3  0        6  0        RunTime          RANK   OVER               PARTITION BY s step name ORDER BY sjh run date DESC                  sjh run time DESC               AS rn          SJH run status     FROM msdb  SYSJobs sj     INNER JOIN msdb  SYSJobHistory sjh ON sj job id   sjh job id     INNER JOIN msdb dbo sysjobsteps s ON sjh job id   s job id         AND sjh step id   s step id     WHERE  sj NAME LIKE  JOB NAME           AND sjh run date   CONVERT CHAR  getdate    112        SELECT   FROM CTE JobStatus WHERE rn   1     AND run status NOT IN  1 4

User · Answer

The most simple way I found was to create a stored procedure  Enter the  JobName  and hit go     -----------------------------------------------------------------------------------------------------------      Document Title  usp getJobStatus      Purpose         Finds a Current Jobs Run Status          Input Example   EXECUTE usp getJobStatus  MyJobName   -------------------------------------------------------------------------------------------------------------        IF OBJECT ID    usp getJobStatus   P    IS NOT NULL     DROP PROCEDURE  usp getJobStatus       GO          CREATE PROCEDURE  usp getJobStatus                             JobName NVARCHAR  1000       AS          IF OBJECT ID  TempDB   JobResults   U   IS NOT NULL DROP TABLE  JobResults         CREATE TABLE  JobResults   Job ID   UNIQUEIDENTIFIER NOT NULL                                      Last Run Date         INT NOT NULL                                      Last Run Time         INT NOT NULL                                      Next Run date         INT NOT NULL                                      Next Run Time         INT NOT NULL                                      Next Run Schedule ID  INT NOT NULL                                      Requested to Run      INT NOT NULL                                     Request Source        INT NOT NULL                                      Request Source id     SYSNAME                                     COLLATE Database Default      NULL                                      Running               INT NOT NULL                                     Current Step          INT NOT NULL                                      Current Retry Attempt INT NOT NULL                                      Job State             INT NOT NULL             INSERT   JobResults          EXECUTE master dbo xp sqlagent enum jobs 1               SELECT  job name                                                AS  Job Name                    SELECT  MAX CAST  STUFF STUFF CAST jh run date AS VARCHAR  7 0  -   5 0  -                                    STUFF STUFF REPLACE STR jh run time 6 0       0   5 0      3 0      AS DATETIME                   FROM    msdb dbo sysjobs AS j                      INNER JOIN msdb dbo sysjobhistory AS jh                         ON jh job id   j job id AND jh step id   0                  WHERE j  name  LIKE        JobName                        GROUP BY j  name                                        AS  Last Completed DateTime                    SELECT  TOP 1 start execution date                  FROM    msdb dbo sysjobactivity                 WHERE   job id   r job id                 ORDER BY start execution date DESC                      AS  Job Start DateTime               CASE                  WHEN r running   0 THEN                     CASE                          WHEN jobInfo lASt run outcome   0 THEN  Failed                          WHEN jobInfo lASt run outcome   1 THEN  Success                          WHEN jobInfo lASt run outcome   3 THEN  Canceled                          ELSE  Unknown                      END                         WHEN r job state   0 THEN  Success                          WHEN r job state   4 THEN  Success                          WHEN r job state   5 THEN  Success                          WHEN r job state   1 THEN  In Progress                          WHEN r job state   2 THEN  In Progress                          WHEN r job state   3 THEN  In Progress                          WHEN r job state   7 THEN  In Progress                       ELSE  Unknown  END                                 AS  Run Status Description          FROM     JobResults AS r              LEFT OUTER JOIN msdb dbo sysjobservers AS jobInfo                 ON r job id   jobInfo job id              INNER JOIN msdb dbo sysjobs AS job                 ON r job id   job job id          WHERE   job  enabled    1                 AND job name LIKE        JobName

User · Answer

This will show last run status time or if running  it shows current run time  step number info  and SPID  if it has associated SPID   It also shows enabled disabled and job user where it converts to NT SID format for unresolved user accounts    CREATE TABLE  list running SQL jobs       job id UNIQUEIDENTIFIER NOT NULL     last run date INT NOT NULL     last run time INT NOT NULL     next run date INT NOT NULL     next run time INT NOT NULL     next run schedule id INT NOT NULL     requested to run INT NOT NULL     request source INT NOT NULL     request source id sysname NULL     running INT NOT NULL     current step INT NOT NULL     current retry attempt INT NOT NULL     job state INT NOT NULL     DECLARE  sqluser NVARCHAR 128           is sysadmin INT   SELECT  is sysadmin   ISNULL IS SRVROLEMEMBER N sysadmin    0    DECLARE read sysjobs for running CURSOR FOR     SELECT DISTINCT SUSER SNAME owner sid FROM msdb dbo sysjobs  OPEN read sysjobs for running  FETCH NEXT FROM read sysjobs for running INTO  sqluser   WHILE   FETCH STATUS   0 BEGIN     INSERT INTO  list running SQL jobs     EXECUTE master dbo xp sqlagent enum jobs  is sysadmin   sqluser      FETCH NEXT FROM read sysjobs for running     INTO  sqluser  END   CLOSE read sysjobs for running  DEALLOCATE read sysjobs for running   SELECT j name         Enbld    CASE j enabled                      WHEN 0                          THEN  no                       ELSE  YES                   END          Min    DATEDIFF MINUTE  a start execution date  ISNULL a stop execution date  GETDATE             Status    CASE                       WHEN a start execution date IS NOT NULL                           AND a stop execution date IS NULL                           THEN  Executing                        WHEN h run status   0                           THEN  FAILED                        WHEN h run status   2                           THEN  Retry                        WHEN h run status   3                           THEN  Canceled                        WHEN h run status   4                           THEN  InProg                        WHEN h run status   1                           THEN  Success                        ELSE  Idle                    END        r current step        spid   p session id        owner   ISNULL SUSER SNAME j owner sid    S-    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1    - CONVERT BIGINT  256    CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256      -    CONVERT NVARCHAR 12   UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   4   1     256   CONVERT BIGINT  NULLIF UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256  0   - CONVERT BIGINT  UNICODE LEFT CONVERT NVARCHAR 256   j owner sid   1     256     ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   5   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   6   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 6   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   7   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   8   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 8   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   9   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   10   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 10   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   11   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   12   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 12   -1     0         ISNULL  -    CONVERT NVARCHAR 12   CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   13   1      CONVERT BIGINT  UNICODE RIGHT LEFT CONVERT NVARCHAR 256   j owner sid   14   1      CONVERT BIGINT  65536    CONVERT BIGINT  NULLIF SIGN LEN CONVERT NVARCHAR 256   j owner sid   - 14   -1     0        --SHOW as NT SID when unresolved        a start execution date        a stop execution date        t subsystem        t step name FROM msdb dbo sysjobs j     LEFT OUTER JOIN  SELECT DISTINCT   FROM  list running SQL jobs  r         ON j job id   r job id     LEFT OUTER JOIN msdb dbo sysjobactivity a         ON j job id   a job id             AND a start execution date IS NOT NULL             --AND a stop execution date IS NULL             AND NOT EXISTS                               SELECT                   FROM msdb dbo sysjobactivity at                 WHERE at job id   a job id                     AND at start execution date  gt  a start execution date                   LEFT OUTER JOIN sys dm exec sessions p         ON p program name LIKE  SQLAgent 0x               AND j job id   SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   7  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   5  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   3  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   1  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   11  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   9  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   15  2    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   13  2     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   17  4     -    SUBSTRING SUBSTRING p program name  CHARINDEX  0x   p program name    2  32   21  12      LEFT OUTER JOIN msdb dbo sysjobhistory h         ON j job id   h job id             AND h instance id   a job history id     LEFT OUTER JOIN msdb dbo sysjobsteps t         ON t job id   j job id             AND t step id   r current step ORDER BY 1   DROP TABLE  list running SQL jobs

User · Answer

This is what I m using to get the running jobs  principally so I can kill the ones which have probably hung    SELECT     job Name  job job ID      job Originating Server      activity run requested Date      datediff minute  activity run requested Date  getdate    AS Elapsed FROM     msdb dbo sysjobs view job          INNER JOIN msdb dbo sysjobactivity activity         ON  job job id   activity job id  WHERE     run Requested date is not null      AND stop execution date is null     AND job name like  Your Job Prefix     As Tim said  the MSDN   BOL documentation is reasonably good on the contents of the sysjobsX tables  Just remember they are tables in MSDB

User · Answer

You haven t specified how would you like to see these details   For the first sight I would suggest to check Server Management Studio   You can see the jobs and current statuses in the SQL Server Agent part  under Jobs  If you pick a job  the Property page shows a link to the Job History  where you can see the start and end time  if there any errors  which step caused the error  and so on   You can specify alerts and notifications to email you or to page you when the job finished successfully or failed   There is a Job Activity Monitor  but actually I never used it  You can have a try   If you want to check it via T-SQL  then I don t know how you can do that

User · Answer

You could try using the system stored procedure sp help job  This returns information on the job  its steps  schedules and servers  For example  EXEC msdb dbo sp help job  Job name    Your Job Name    SQL Books Online should contain lots of information about the records it returns   For returning information on multiple jobs  you could try querying the following system tables which hold the various bits of information on the job   msdb dbo SysJobs msdb dbo SysJobSteps msdb dbo SysJobSchedules  msdb dbo SysJobServers  msdb dbo SysJobHistory   Their names are fairly self-explanatory  apart from SysJobServers which hold information on when the job last run and the outcome    Again  information on the fields can be found at MSDN  For example  check out the page for SysJobs

User · Answer

SELECT sj name   FROM msdb  sysjobactivity aj   JOIN msdb  sysjobs sj     on sj job id   aj job id  WHERE aj stop execution date  IS NULL     -- job hasn t stopped running    AND aj start execution date IS NOT NULL -- job is currently running    AND sj name     lt your Job Name gt      AND NOT EXISTS  -- make sure this is the most recent run                    select 1                      from msdb  sysjobactivity new                     where new job id   aj job id                       and new start execution date  gt  aj start execution date     print  running

User · Answer

Below script gets job status for every job on the server  It also tells how many steps are there and what is the currently running step and elasped time   SELECT sj Name      CASE         WHEN sja start execution date IS NULL THEN  Never ran          WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL THEN  Running          WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NOT NULL THEN  Not running      END AS  RunStatus       CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then js StepCount else null end As TotalNumberOfSteps      CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then ISNULL sja last executed step id 1 js StepCount  else null end as currentlyExecutingStep      CASE WHEN sja start execution date IS NOT NULL AND sja stop execution date IS NULL then datediff minute  sja run requested date  getdate    ELSE NULL end as ElapsedTime FROM msdb dbo sysjobs sj JOIN msdb dbo sysjobactivity sja ON sj job id   sja job id CROSS APPLY  SELECT COUNT    FROM msdb dbo sysjobsteps as js WHERE js job id   sj job id  as js StepCount  WHERE session id         SELECT MAX session id  FROM msdb dbo sysjobactivity  ORDER BY RunStatus desc

User · Answer

-- Microsoft SQL Server 2008 Standard Edition  IF EXISTS SELECT 1            FROM msdb dbo sysjobs J            JOIN msdb dbo sysjobactivity A                ON A job id J job id            WHERE J name N Your Job Name             AND A run requested date IS NOT NULL            AND A stop execution date IS NULL                PRINT  The job is running   ELSE     PRINT  The job is not running

User · Answer

I ran into issues on one of my servers querying MSDB tables  aka code listed above  as one of my jobs would come up running  but it was not   There is a system stored procedure that returns the execution status  but one cannot do a insert exec statement without an error   Inside that is another system stored procedure that can be used with an insert exec statement   INSERT INTO  Job EXEC master dbo xp sqlagent enum jobs 1 dbo   And the table to load it into   CREATE TABLE  Job                  job id               UNIQUEIDENTIFIER NOT NULL                   last run date         INT              NOT NULL                   last run time         INT              NOT NULL                   next run date         INT              NOT NULL                   next run time         INT              NOT NULL                   next run schedule id  INT              NOT NULL                   requested to run      INT              NOT NULL  -- BOOL                  request source        INT              NOT NULL                   request source id     sysname          COLLATE database default NULL                   running               INT              NOT NULL  -- BOOL                  current step          INT              NOT NULL                   current retry attempt INT              NOT NULL                   job state             INT              NOT NULL

User · Answer

we can query the msdb in many ways to get the details   few are  select job Name  job job ID  job Originating Server activity run requested Date  datediff minute  activity run requested Date  getdate    as Elapsed  from msdb dbo sysjobs view job  inner join msdb dbo sysjobactivity activity on  job job id   activity job id   where run Requested date is not null  and stop execution date is null  and job name like  Your Job Prefix

User · Answer

This is what I m using to get the running jobs  principally so I can kill the ones which have probably hung    SELECT     job Name  job job ID      job Originating Server      activity run requested Date      datediff minute  activity run requested Date  getdate    AS Elapsed FROM     msdb dbo sysjobs view job          INNER JOIN msdb dbo sysjobactivity activity         ON  job job id   activity job id  WHERE     run Requested date is not null      AND stop execution date is null     AND job name like  Your Job Prefix     As Tim said  the MSDN   BOL documentation is reasonably good on the contents of the sysjobsX tables  Just remember they are tables in MSDB

[sql] How can I determine the status of a job?

Examples related to sql

Examples related to sql-server-2005

Examples related to tsql

Examples related to stored-procedures