Determining resource owners | SQL Server Performance Forums

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 (, 1, 4) As Type,<br />substring (rsc_text, 1, 16) as Resource,<br />substring (, 1, <img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> As Mode,<br />substring (, 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 =<br />WHEREsd.dbid = 17 –our database<br />and = 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
CREATE PROCEDURE sp_leadblocker
(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
SELECT ‘No blocking processes found!’
HTH. Gaurav
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.