Need to see how long job has been running? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Need to see how long job has been running?

Author

Topic

Tapalotapus
Starting Member

22 Posts

Posted - 2005-03-18 : 16:17:01

I need to find a way to see how long a job is currently running?

I know the SYSJOBS tables will tell me how long it took the last time it was run. What I want to do is look and see if it is currently running and if it has been for over 5 minutes.

Thanks

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-19 : 09:21:39

Yes, all those job status constants (and so on) are really confusing.
And I can suggest the following lightweight approach for the subject:

while the job is executing we can't determine when exactly it started. But if its
1st step already completed then this event is logged into sysjobhistory table!

So, I suggest to insert into the job a dummy active script task (e.g., x=5) as the
job's very first step. This step costs nothing and will be executed instantly.
But now we can determine almost exactly when the job (as a whole) was started.

use msdb
go
declare
@job_name sysname,
@j varchar(17), @jdt datetime,
@s varchar(17), @sdt datetime

set @job_name='myJob'

/*
select top 1
@j=cast(last_run_date as varchar(8))+' '+right('00000'+last_run_time,6),
@s=cast(run_date as varchar(8))+' '+right('00000'+run_time,6)
from
*/

select top 1
@j = right('1999111' + cast(last_run_date as varchar(8)), 8) + ' ' +
right('00000' + cast(last_run_time as varchar(6)), 6),
@s = right('1999111' + cast(run_date as varchar(8)), 8) + ' ' +
right('00000' + cast(run_time as varchar(6)), 6)
from
sysjobs j (nolock), sysjobhistory h (nolock), sysjobservers s (nolock)
where
j.name=@job_name and h.job_id=j.job_id and s.job_id=j.job_id and
step_id=1
order by instance_id desc

select
@jdt=cast(stuff(stuff(@j,12,0,':'),15,0,':') as datetime),
@sdt=cast(stuff(stuff(@s,12,0,':'),15,0,':') as datetime)

if datediff(ss,@jdt,@sdt)>5
print datediff(mi,@sdt,getdate()) -- how many minutes it's running...
else
print 'Job '''+@job_name+''' is idle.'

===================
if datediff(ss,@jdt,@sdt)>5 seconds MEANS the job is currently
RUNNING and @jdt is the start datetime of PREVIOUS/LAST job's run.
Edit:
Hardly it can happen that STEP1_start_datetime will be greater than
JOB_start_datetime by more than 5 seconds. Usually they are equal,
with 1 sec accuracy.

Tapalotapus
Starting Member

22 Posts

Posted - 2005-03-20 : 01:49:37

That works for me, thanks. Is this how SQL server figures out it's Run Duration?

Thanks again!

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-20 : 05:38:54

There is also
exec master.dbo.xp_sqlagent_enum_jobs 1,''
this will also show You the jobs that are currently running, but You can't see what time they were started.

rockmoose

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-03-20 : 10:08:55

I usually build custom logging into the job steps (either to file or a table). You can get more info that way including record counts, durations, etc. Plus you can monitor the log while the job is processing and keep a history of those details.

Be One with the Optimizer
TG

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-03-20 : 12:54:19

Oops!

select top 1
@j=cast(last_run_date as varchar(8))+' '+right('00000'+last_run_time,6),
@s=cast(run_date as varchar(8))+' '+right('00000'+run_time,6)
from

SHOULD BE:

select top 1
@j = right('1999111' + cast(last_run_date as varchar(8)), 8) + ' ' +
right('00000' + cast(last_run_time as varchar(6)), 6),
@s = right('1999111' + cast(run_date as varchar(8)), 8) + ' ' +
right('00000' + cast(run_time as varchar(6)), 6)
from

because run_times are INTEGERS and e.g. 00:22:33 AM will be 2233
and initial values of run_dates are ZEROS.

rockmoose
SQL Natt Alfen

3279 Posts

Posted - 2005-03-20 : 12:55:06

I would also recommend TG's approach.
We do quite a lot of logging in our stored procedures and that makes it possible
to follow the execution of the jobs quite efficiently.
Also badly performing queries are easily detected, and errors easily identified.

All DTS packages are started within sp's via xp_cmdshell + dtsrun, so they are monitored as well.
(it's more of a PIA to log within dts packages, but we do that where we think it i necessary).

rockmoose

Tapalotapus
Starting Member

22 Posts

Posted - 2005-03-20 : 19:44:22

Thanks guys good info.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources