--	Create By:	Regan Galbraith
--	Create On:	2003-01-04
--	Purpose:	
--		This procedure was written to determine SPIDS that have held locks for a long duration. I have not 
--		been able to check shorter than minutes
--		
--
--	Example:
--		exec sp_long_locks 'user_db_name',5
--
--	Change Control:	version 1 - creation and adding of comment
--
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_long_locks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_long_locks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO



CREATE proc sp_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