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?
Msg 208, Level 16, State 1, Line 1Invalid object name 'dbo.sysjobs_view'.
KM
2009-09-24 13:15:39
Check permissions/server version
Jim
2009-09-24 13:22:51
I executed the query and i got the details thank you very much for your help.
srihari
2009-09-24 13:30:36
@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
2009-09-24 13:54:19
Hi KM,To be fair I read the question as "When was the job created on the server?" rather than "When did it run?"
Jim
2009-09-24 14:32:42
ha, I guess that the OP read it that way too ;-) as you got the "correct answer"!
KM
2009-09-24 14:49:36
A:
using management studios, you can right click the job and click view history. this will containt the list of executions for the job.
DForck42
2009-09-24 13:19:27
This job is a continous job and is cycling execution status for every one minute.Thank you very much for the advise.
srihari
2009-09-24 13:29:33
+1
A:
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
2009-09-24 13:50:44