February 2, 2007 at 5:10 am
HI All
CAn someone help me with the following?
To see how long a job has run, you usually view its View Job History. Is there maybe a script that shows how long a job; that has been started, runs? It's current run time...
PLEASE...???
Thanks
Anchelin
February 2, 2007 at 5:33 am
just look at the sysjobhistory tables u will find the step wise details of the job. the sttus, start time, runtime, next start time. etc
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 2, 2007 at 5:48 am
Hi
Yes, but I want to know if there is a way to see the current run time of a job while it is executing. Like if I start the job and someone asks me how long its been running.... is there a way to view it?
Anchelin
February 2, 2007 at 8:06 am
Maybe query the sys.sysprocesses view?
Looks like someone might have built a solution to what you're asking at: http://www.tek-tips.com/viewthread.cfm?qid=1328109&page=2
-- http://dbachman.blogspot.com
February 2, 2007 at 1:34 pm
The following script gives you the details with step execution times...
You can modify based on your needs...
select step_name,'run_date'= cast(h.run_date as varchar(8)),
'run_time'= replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),
'run_datetime' = left(cast(h.run_date as varchar(8)),4)+'/'
+substring(cast(h.run_date as varchar(8)),5,2)+'/'
+right(cast(h.run_date as varchar(8)),2)+' '
+left(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2)+':'
+substring(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),3,2)+':'
+right(replicate('0',6-len(h.run_time))+cast(h.run_time as varchar(6)),2),
--run_duration = cast(h.run_duration as varchar(20)),
run_duration_InSeconds = case
when (len(cast(h.run_duration as varchar(20))) < 3)
then cast(h.run_duration as varchar(6))
WHEN (len(cast(h.run_duration as varchar(20))) = 3)
then LEFT(cast(h.run_duration as varchar(6)),1) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) = 4)
then LEFT(cast(h.run_duration as varchar(6)),2) * 60 --min
+ RIGHT(cast(h.run_duration as varchar(6)),2) --sec
WHEN (len(cast(h.run_duration as varchar(20))) >= 5)
then (Left(cast(h.run_duration as varchar(20)),len(h.run_duration)-4)) * 3600 --hour
+(substring(cast(h.run_duration as varchar(20)) , len(h.run_duration)-3, 2)) * 60 --min
+ Right(cast(h.run_duration as varchar(20)) , 2) --sec
end from msdb..sysjobhistory h where job_id =
(select job_id from msdb..sysjobs where name = 'job name')
MohammedU
Microsoft SQL Server MVP
February 2, 2007 at 1:51 pm
THANK YOU Mohammed
MUCH APPRECIATED!!!!
Anchelin
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply