1

A job running on our SQL server failed. We are running MS SQL server 2005. While investigating, the following question came up: When was this process initiated on the server? Is there any query I can run that will give me this information?

Adam Bellaire
  • 108,003
  • 19
  • 148
  • 163
Srihari
  • 2,509
  • 5
  • 30
  • 34

3 Answers3

3

This should give you what you need

SELECT 
    Jobs.name, 
    StartTime = CONVERT 
        ( 
            DATETIME, 
            RTRIM(run_date) 
            ) 
            +  
            ( 
            run_time * 9 
            + run_time % 10000 * 6 
            + run_time % 100 * 10 
        ) / 216e4 
    ,
    endTime = CONVERT 
        ( 
            DATETIME, 
            RTRIM(run_date) 
            ) 
            +  
            ( 
            run_time * 9 
            + run_time % 10000 * 6 
            + run_time % 100 * 10 
            + 25 * run_duration 
        ) / 216e4 
FROM 
    msdb..sysjobhistory JobHistory 
    INNER JOIN msdb..sysjobs Jobs 
        ON Jobs.job_id = JobHistory.job_id 
WHERE 
JobHistory.step_name = '(Job outcome)'
Raj More
  • 47,048
  • 33
  • 131
  • 198
0

USE msdb SELECT * FROM dbo.sysjobs_view

BIDeveloper
  • 2,628
  • 4
  • 36
  • 51
  • Msg 208, Level 16, State 1, Line 1 Invalid object name 'dbo.sysjobs_view'. – KM. Sep 24 '09 at 13:15
  • Check permissions/server version – BIDeveloper Sep 24 '09 at 13:22
  • I executed the query and i got the details thank you very much for your help. – Srihari Sep 24 '09 at 13:30
  • @Jim, I'm running as "sa" on SQL Server 2005. However, I just looked at the _FROM_ portion of your answer and took the table name, completely missing the _USE msdb_ so the table did not exist in my current db. I prefer writing the query as __SELECT * FROM msdb.dbo.sysjobs_view__ and not using a _USE_. What column contains the "start time of the SQL process"? – KM. Sep 24 '09 at 13:54
  • Hi KM, To be fair I read the question as "When was the job created on the server?" rather than "When did it run?" – BIDeveloper Sep 24 '09 at 14:32
  • ha, I guess that the OP read it that way too ;-) as you got the "correct answer"! – KM. Sep 24 '09 at 14:49
0

using management studios, you can right click the job and click view history. this will containt the list of executions for the job.

DForck42
  • 19,789
  • 13
  • 59
  • 84
  • This job is a continous job and is cycling execution status for every one minute.Thank you very much for the advise. – Srihari Sep 24 '09 at 13:29