SQL Server Job Run Duration

[AppSpecialist]

I need to be able to query SQL Server jobs to see how long a job has been running... primarily for jobs that are currently running.

Does anyone know of a SQL Statement that will do this?

 

[smnetto]

You can get information about jobs (last run date, duration,...) using a query like this:

use msdb

select * from sysjobs a join sysjobservers b on a.job_id=b.job_id
where a.enabled=1 order by last_run_date,last_run_time desc

Sergio - DBA SQL

 

[AppSpecialist]

Thanks for the response, but I need to be be able to query information on the jobs that are running.

Primarily the reason for this is I am finding more and more that some jobs are being left in an executing state and never finish. So I want to be able to run a query outside SQL Server and check the duration of jobs that are currently running.

If the duration is extremely long... for example 3 hours for a job that usually takes 5 minutes, the script will alert me of this situation.

However, I don;t know where I can find information on current execution duration.

Thanks

 

[SQLBill]

Take a look at BOL, use the index tab, enter JOBS. Pick the entry for Information Stored in System Table.

Specifically, the table you want is sysjobhistory and the fields you are most interested in is RUN_STATUS = 4 (in progress) and RUN_DURATION.

-SQLBill

Posting advice: FAQ481-4875

 

[AppSpecialist]

That is what I thought too... but I never see the job with run status of 4.

I just did a test, started a job that runs a DTS package and never see the job with a run status of 4... Actually I do not see that job listed until t completes. Then it shows with a run status of 1.

 

[smnetto]

Wow, now i understand (u only want duration for jobs in execution) - OK - Try this:


job_state:

0 Returns only those jobs that are not idle or suspended.
1 Executing.
2 Waiting for thread.
3 Between retries.
4 Idle.
5 Suspended.
7 Performing completion actions.

So we need status=1 (Executing)


create table #tmp_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

declare @is_sysadmin INT
declare @job_owner sysname
set @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
set @job_owner = SUSER_SNAME()
insert into #tmp_jobs execute master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner

select a.name,b.running,b.job_state from sysjobs a join #tmp_jobs b on a.job_id=b.job_id
where a.enabled=1 and b.job_state=1

--To see all that not running use ..... and b.job_state=4



Now i thing this will help ^^

Sergio - DBA SQL

 

[AppSpecialist]

That almost does it... but still does not show the duration of the current running job, which is key for me.

 

[smnetto]

OK, so u will need to create a user function to get it:

CREATE function fn_hex_to_char (
@x varbinary(100), -- binary hex value
@l int -- number of bytes
) returns varchar(200)
as
begin

declare @i varbinary(10)
declare @digits char(16)
set @digits = '0123456789ABCDEF'
declare @s varchar(100)
declare @h varchar(100)
declare @j int
set @j = 0
set @h = ''
-- process all bytes
while @j < @l
begin
set @j= @j + 1
-- get first character of byte
set @i = substring(cast(@x as varbinary(100)),@j,1)
-- get the first character
set @s = cast(substring(@digits,@i%16+1,1) as char(1))
-- shift over one character
set @i = @i/16
-- get the second character
set @s = cast(substring(@digits,@i%16+1,1) as char(1)) + @s
-- build string of hex characters
set @h = @h + @s
end
return(@h)
end

GO


create table #tmp_jobs (job_id UNIQUEIDENTIFIER NOT NULL,
last_run_date INT NOT NULL,
last_run_time INT NOT NULL,
next_run_date INT NOT NULL,
next_run_time INT NOT NULL,
next_run_schedule_id INT NOT NULL,
requested_to_run INT NOT NULL, -- BOOL
request_source INT NOT NULL,
request_source_id sysname COLLATE database_default NULL,
running INT NOT NULL, -- BOOL
current_step INT NOT NULL,
current_retry_attempt INT NOT NULL,
job_state INT NOT NULL)

declare @is_sysadmin INT
declare @job_owner sysname
set @is_sysadmin = ISNULL(IS_SRVROLEMEMBER(N'sysadmin'), 0)
set @job_owner = SUSER_SNAME()
insert into #tmp_jobs execute master.dbo.xp_sqlagent_enum_jobs @is_sysadmin, @job_owner


select a.name,b.running,b.job_state,(convert(varchar(8),getdate()-c.login_time,108)) as 'duration' from sysjobs a join #tmp_jobs b on a.job_id=b.job_id
join master.dbo.sysprocesses c on substring(c.program_name,32,32)= msdb.dbo.fn_hex_to_char(a.job_id,16)
where a.enabled=1 and b.job_state=1

 

[smnetto]

I forgot, u only need to create function 1 time.

So to see the duration use the script create table #tmp_jobs...... to the end

OK.

Sergio - DBA SQL

 

[vongrunt]

Regarding hex-to-char UDF...

fn_varbintohexstr() (master DB) does pretty much the same thing

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]