SQL Server Performance Forum – Threads Archive
Determining resource owners
I use a stored procedure shown below to reflect the current locks on a specific table within a specific database, is there anyway I can tell add in a column to be able to identify what the resource owner is? i.e. I am trying to identify the stored procedure which initially took out the lock and is still holding it?<br /><br />Thanks in advance<br /><br /><i>CREATE PROCEDURE usp_OutputLocks2 <br />( @pi_spname varchar(25)<br />)<br />AS<br />declare @vdate datetime<br />set @vdate = getdate()<br />insert into tmpLock<br />SELECT @vdate,convert (smallint, req_spid) As spid, <br />rsc_dbid As dbid,<br />rsc_objid As ObjId,<br />so.Name As ObjectName,<br />rsc_indid As IndId,<br />substring (v.name, 1, 4) As Type,<br />substring (rsc_text, 1, 16) as Resource,<br />substring (u.name, 1, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> As Mode,<br />substring (x.name, 1, 5) As Status, <br />req_ownertype as ownertype<br /><br /><br />FROM master.dbo.syslockinfo sli<br />JOINmaster.dbo.spt_values v<br /> ONsli.rsc_type = v.number<br /> ANDv.type = ‘LR'<br />JOINmaster.dbo.spt_values x<br /> ONsli.req_status = x.number<br /> ANDx.type = ‘LS'<br />JOINmaster.dbo.spt_values u<br /> ONsli.req_mode + 1 = u.number<br /> ANDu.type = ‘L'<br />JOINmaster.dbo.sysdatabases sd<br /> ONsli.rsc_dbid = sd.dbid<br />JOINichobs_processpayments.dbo.sysobjects so<br /> ONsli.rsc_objid = so.id<br />WHEREsd.dbid = 17 –our database<br />and so.id = 1495676376 — paymenttemplate table<br /><br />GO<br /> </i>If you are trying to see the spid which is the cause of blocking, the script is in Inside SQL Server 2000.
USE master
GO
CREATE PROCEDURE sp_leadblocker
AS
IF EXISTS
(SELECT * FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses))
SELECT spid, status, loginame=SUBSTRING(loginame,1,12),
hostname=substring(hostname, 1, 12),
blk=CONVERT(char(3), blocked),
dbname=SUBSTRING(DB_NAME(dbid), 1, 10), cmd, waittype
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked=0
ELSE
SELECT ‘No blocking processes found!’
HTH. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
you could open a trace and add to it deadlock and deadlock chain and you will be able to see those deadlocks and who’s causing them.
You could also take a look at sp_lock and sp_who2 that will show you the list of processed and indicate if current process is blocked and the blocking process id.
You could then run dbcc inputbuffer(spid) on the blocking processes. Bambola.
]]>