February 22, 2005 at 11:59 am
Hi,
I am trying to write a query on the jobs system tables to show me all jobs that are currently running for too long a time, over a certain duration limit. I have looked at the system tables and the sp_help_job stored procedure but I can't quite see how to do it.
Thanks for any help,
Diane Davis
February 24, 2005 at 3:43 am
Would this help you at all ... I wrote it quite a while ago - I comment better now... will revisit at some time and update, but it should work, or you can strip it out of a SP and do it as a query.
I used the sp_ just so I could run it from anywhere, since I created it in master. Consider if you want to do that....
***************************code**********************
Use [Master]
go
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_ast_long_locks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_ast_long_locks]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE proc sp_ast_long_locks
@database sysname,
@duration decimal(15,5)
as
SET NOCOUNT ON
--select @duration
set @duration = (@duration / 1440)
--select @duration
--if object exists, then check for locks. First create a tempt table to get the data from the target database
create table #locks
( resource_type varchar(8),
method_of_lock varchar(8),
lock_request_status varchar(10),
lock_request_owner_spid integer,
resource_objid integer,
objectname sysname null)
SET QUOTED_IDENTIFIER OFF
-- setup lock string for selecting from target database
declare @sel varchar(128)
declare @ect varchar(128)
set @sel = 'insert into #locks select rsc_type,req_mode,req_status,req_spid,rsc_objid,so.name from
master..syslockinfo sl left join '
set @ect = '..sysobjects so on sl.rsc_objid = so.id where sl.rsc_type <= 9 and rsc_type <> 2'
exec (@sel + @database + @ect)
create table #long_locks
( process_id integer,
duration decimal(15,5))
insert into #long_locks
select
distinct
spid,
cast( (getdate()-last_batch) as decimal(15,4) ) as duration
from sysprocesses sp,
#locks l
where l.lock_request_owner_spid = sp.spid
and (cast( (getdate()-last_batch) as decimal(15,4) ) ) >= (@duration)
order by 2 desc
--select * from #locks
--select * from #long_locks
if (select count(*) from #long_locks) = 0
begin
print 'There are no locks longer than '+ltrim(str(@duration*1440))+ ' minutes, currently'
goto no_locks
end
SET NOCOUNT OFF
--display process information regarding the processes that have locks against the target object, in the target database
--select count(*) from locks
select
convert(varchar(8),sp.nt_username) as UserId,
convert(varchar(20),sp.loginame) as SQL_LoginName,
sp.spid as process_id,
left(l.objectname,30) as objectname,
case l.resource_type
when 1 then 'null'
when 2 then 'database'
when 3 then 'file'
when 4 then 'index'
when 5 then 'table'
when 6 then 'page'
when 7 then 'key'
when 8 then 'extent'
when 9 then 'RID'
end
as resource_type,
case l.method_of_lock
when 1 then 'Sch-S'
when 2 then 'Sch-M'
when 3 then 'S'
when 4 then 'U'
when 5 then 'X'
when 6 then 'IS'
when 7 then 'IU'
when 8 then 'IX'
when 9 then 'SIU'
when 10 then 'SIX'
when 11 then 'UIX'
when 12 then 'BU'
when 13 then 'RangeS_S'
when 14 then 'RangeS_U'
when 15 then 'RangeI_N'
when 16 then 'RangeI_S'
when 17 then 'RangeI_U'
when 18 then 'RangeI_X'
when 19 then 'RangeX_S'
when 20 then 'RangeX_U'
when 21 then 'RangeX_X'
end
as method_of_lock,
case l.lock_request_status
when 1 then 'granted'
when 2 then 'converting'
when 3 then 'waiting'
end
as lock_request_status,
(ll.duration * 1440) as duration_of_lock_min,
sp.blocked,
sp.login_time,
sp.last_batch as last_batch_time,
convert(varchar(15),sp.hostname) as hostname,
convert(varchar(30),sp.program_name) as program_name,
getdate() as datetime_checked,
left(@@servername,20) as server_checked
from master..sysprocesses sp
join #locks l
on sp.spid = l.lock_request_owner_spid
join #long_locks ll
on sp.spid = ll.process_id
where sp.spid = l.lock_request_owner_spid
and l.lock_request_owner_spid = ll.process_id
order by resource_type desc,ll.duration desc
no_locks:
drop table #locks
drop table #long_locks
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
--example - you can use CTRL-SHIFT-M to replace the template.
--sp_ast_long_locks '<DBname,sysname,Your Vaorite DBName>','0'
***************************code**********************
Let me know if you find it useful...
February 24, 2005 at 3:45 am
NOTE - that is for spids effectively - I am hoping you can use if to find you jobs
February 24, 2005 at 12:27 pm
This is interesting, but I am pursuing a solution with the sysjobs and sysjobhistory tables. Thanks, though. DD
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply