Home > Catalog Views, Management Studio, SQL Agent, SQLServer > SQL Server – Check SQL Agent Job History using T-SQL

SQL Server – Check SQL Agent Job History using T-SQL

To check SQL Server Agent Job History you can use the Log File Viewer from SQL Server Management Studio.

To Open Log File Viewer,

1. Expand Server Node > 2. Expand SQL Server Agent > 3. Expand Jobs > 4. Right click on the Job and 5. Select "View History" as shown in the screen shot below:

image

 

Alternatively, you can also use below T-SQL code to check Job History:

SELECT      [JobName]   = JOB.name,

            [Step]      = HIST.step_id,

            [StepName]  = HIST.step_name,

            [Message]   = HIST.message,

            [Status]    = CASE WHEN HIST.run_status = 0 THEN 'Failed'

            WHEN HIST.run_status = 1 THEN 'Succeeded'

            WHEN HIST.run_status = 2 THEN 'Retry'

            WHEN HIST.run_status = 3 THEN 'Canceled'

            END,

            [RunDate]   = HIST.run_date,

            [RunTime]   = HIST.run_time,

            [Duration]  = HIST.run_duration

FROM        sysjobs JOB

INNER JOIN  sysjobhistory HIST ON HIST.job_id = JOB.job_id

/* WHERE    JOB.name = 'Job1' */

ORDER BY    HIST.run_date, HIST.run_time

Output:

JobName  Step StepName      Message       Status     RunDate   RunTime   Duration

Job1     1    Step1         Executed a..  Succeeded  20120416  173935    10

Job1     0    (Job outcome) The job su..  Succeeded  20120416  173935    10

Job2     1    Step1         Executed a..  Succeeded  20120416  174037    10

Job2     0    (Job outcome) The job su..  Succeeded  20120416  174037    10

 

Check dbo.sysjobs and dbo.sysjobhistory on BOL for more information.

Hope This Helps!

Vishal

If you like this post, do like my Facebook Page -> SqlAndMe

EMail me your questions -> Vishal@SqlAndMe.com
Follow me on Twitter -> @SqlAndMe

About these ads
  1. anil
    January 10, 2013 at 12:13 pm

    Thanks it was very useful

  2. Dan
    August 5, 2013 at 10:51 pm

    I’d add
    WHEN HIST.run_status = 4 THEN ‘In-progress’
    WHEN HIST.run_status = 5 THEN ‘Unknown’
    to the Case statement.
    I’d also add “, HIST.step_id” to the Order By clause, just to keep the results easy to understand.

  3. March 13, 2014 at 2:51 pm

    It’s going to be end of mine day, but before ending I am reading this wonderful paragraph to increase my knowledge.

  1. July 22, 2013 at 5:48 pm
  2. July 22, 2013 at 6:02 pm

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 273 other followers

%d bloggers like this: