Skip to content
Archive of posts tagged blocking

Blocking Processes (lead blocker)

17 June 2009, 9:53 pm

SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0

Filed under Scripts, Troubleshooting. Tagged 
Comments

Find Resource Currently Involved in Blocking

31 May 2009, 11:38 pm

SELECT DISTINCT
objname = object_name(p.object_id)
FROM sys.partitions p
JOIN sys.dm_tran_locks t1
ON p.hobt_id = t1.resource_associated_entity_id

Filed under DMV Queries. Tagged ,
Comments