Script: Get inputbuffer for spid's holding locks

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

General SQL Server Forums

Script Library

Script: Get inputbuffer for spid's holding locks

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-07-20 : 15:17:17

Here's a quick script that will return the inputbuffer of processes that are holding more than a certain threshold of locks (by default, 10, but it's easy to modify). Handy for tracking down what query is holding those 1400 locks.

Yes, it uses a cursor. I'd welcome amendment by anyone who has the insight on converting it to pure set-based.

Cheers
-b

CREATE PROCEDURE sp_lock4 AS
set nocount on

DECLARE @tSpids table(spid int PRIMARY KEY CLUSTERED,count int)
DECLARE @iSpid int,@iCount int

	insert into @tSpids (spid,count)
	select 	convert (smallint, req_spid) As spid,
		count(*) as count

	from 	master.dbo.syslockinfo,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u

	where   master.dbo.syslockinfo.rsc_type = v.number
			and v.type = 'LR'
			and master.dbo.syslockinfo.req_status = x.number
			and x.type = 'LS'
			and master.dbo.syslockinfo.req_mode + 1 = u.number
			and u.type = 'L'
	group by converT(smallint,req_spid),		'dbcc inputbuffer(' + cast(req_spid as varchar(4)) + ')'
	having count(*)>10
	order by count(*) desc

DECLARE cLoop cursor for 
  select spid,count from @tSpids

OPEN cLoop

FETCH NEXT FROM cLoop INTO @iSpid,@iCount
WHILE @@FETCH_STATUS=0
  BEGIN
    select 'spid ' + cast(@iSpid as varchar(4)) + ' has ' + cast(@iCount as varchar(5)) + ' locks.'
    exec ("dbcc inputbuffer (" + @ispid + ")")
    FETCH NEXT FROM cLoop INTO @iSpid,@iCount
  END

CLOSE cLoop
DEALLOCATE cLoop  

return (0) -- sp_lock
GO

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-07-21 : 12:45:51

Here's an improved version which also reports the object ID and object name of the object(s) that a process is holding locks on. Much easier to find the problem :)

Cheers
-b

CREATE PROCEDURE sp_lock4 AS
set nocount on

DECLARE @tSpids table(spid int PRIMARY KEY CLUSTERED (spid,objid),
                                        dbid int,objid int,count int)
DECLARE @iSpid int,@dbID int,@objID int,@iCount int,@vcDBCC varchar(100),@vcName varchar(50)

	insert into @tSpids (spid,dbid,objid,count)
	select 	convert (smallint, req_spid) As spid,
		rsc_dbid,
		rsc_objid,
		count(*) as count

	from 	master.dbo.syslockinfo,
		master.dbo.spt_values v,
		master.dbo.spt_values x,
		master.dbo.spt_values u

	where   master.dbo.syslockinfo.rsc_type = v.number
			and v.type = 'LR'
			and master.dbo.syslockinfo.req_status = x.number
			and x.type = 'LS'
			and master.dbo.syslockinfo.req_mode + 1 = u.number
			and u.type = 'L'
	group by converT(smallint,req_spid),rsc_dbid,rsc_objid
	having count(*)>10
	order by count(*) desc

DECLARE cLoop cursor for 
  select spid,dbid,objid,count from @tSpids

OPEN cLoop

FETCH NEXT FROM cLoop INTO @iSpid,@dbID,@objID,@iCount
WHILE @@FETCH_STATUS=0
  BEGIN
    select @vcName=IsNull(name,'unknown') from sysobjects
        where id=cast(@objID as varchar(32))


    select cast(@iSpid as varchar(4)) + ' has ' + cast(@iCount as varchar(5)) + ' locks on object ' + cast(@objid as varchar(20)) + ' (' + @vcName + ')'
    select @vcDBCC='dbcc inputbuffer(' + cast(@iSpid as varchar(5)) + ')'
    exec (@vcDBCC)
    FETCH NEXT FROM cLoop INTO @iSpid,@dbID,@objID,@iCount
  END

CLOSE cLoop
DEALLOCATE cLoop  

return (0) -- sp_lock
GO

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources