June 4, 2008 at 12:20 am
Hello Gurus
I am using sql 2005 and one job status is executing in job monitor in 2005,How can i check since how long this job is running?i have manyally started this job
Please advice
Nitin
June 4, 2008 at 1:00 am
Hey create this procedure in msdb db and exec as
EXEC usp_job_history '10/02/2004'
------------------
CREATE PROCEDURE usp_job_history
@dateparam DATETIME
AS
SELECT dbo.sysjobhistory.server, dbo.sysjobs.name AS job_name,
CASE dbo.sysjobhistory.run_status
WHEN 0 THEN 'Failed'
WHEN 1 THEN 'Succeeded'
ELSE '???'
END as run_status, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobhistory.step_id, dbo.sysjobhistory.step_name, dbo.sysjobhistory.run_duration, dbo.sysjobhistory.message
FROM dbo.sysjobhistory INNER JOIN
dbo.sysjobs ON dbo.sysjobhistory.job_id = dbo.sysjobs.job_id
WHERE dbo.sysjobs.category_id = 0 and dbo.sysjobhistory.run_date = datepart(yyyy,@dateparam)*10000 + datepart(mm,@dateparam)*100 + datepart(dd,@dateparam)
ORDER BY dbo.sysjobhistory.server, dbo.sysjobhistory.run_date, dbo.sysjobhistory.run_time, dbo.sysjobs.name, dbo.sysjobhistory.step_id
GO
January 14, 2011 at 12:31 am
Perhaps I am missing something but this stored-procedure only tells you historically the last time a SQL Agent job started and finished, it does not tell you the start time of a job that is currently running. For instance, if I started a job a 6am this morning and it complete 2 hours later, it shows up in the resultset for your query. However, if my co-worker kicked the same job off again at 3pm...there's no record of it whatsoever.
Am I missing something big here?
______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience
January 14, 2011 at 5:19 am
Try to look in activity monitor, start_time, last_batch, wait_time of your process.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply