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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Script: Get inputbuffer for spid's holding locks
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

aiken
Aged Yak Warrior

USA
525 Posts

Posted - 07/20/2002 :  15:17:17  Show Profile  Send aiken an ICQ Message  Reply with Quote
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

USA
525 Posts

Posted - 07/21/2002 :  12:45:51  Show Profile  Send aiken an ICQ Message  Reply with Quote
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


Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000