Intru si eu in discutie, poate cu ocazia asta ma mai lamuresc. Tot probleme cu blocaje pe un server SQL 2005. Dupa indelungi cautari si incercari am facut o procedura stocata care imi returneaza informatii despre cine blocheaza si pe cine blocheaza. Problema e ca nu reusesc sa adaptez procedura sa vad exact ce parametri sunt folositi. Dau un exemplu:
Query1:
create table ##TblTest (SomeColumn int)
go
begin transaction
insert into ##TblTest (SomeColumn) values (1)

waitfor delay '0:01:40' /* hold the transaction open for 40 seconds */
go
commit transaction

Query2:
SELECT * FROM ##tblTest WHERE somecolumn=1

La rularea procedurii imi apare ca primul query il blocheaza pe al doilea.Folosing sys.dm_exec_sql_text imi la al doilea proces imi afiseaza query-ul ca fiind:
(@1 tinyint)SELECT * FROM [##tblTest] WHERE [somecolumn]=@1
Deci, nu stiu ce valoare are @1. Am incercat si cu Inputbuffer dar tot nu afiseaza ce se intampla pentru cazurile 'real life' de ex, cand se salveaza o factura, se executa o procedura... totul e cu parametri @x

Atasez procedura poate aveti mai multa inspiratie:

Create PROCEDURE [dbo].[sp_GetBlockingStatus]
AS

BEGIN
SET NOCOUNT ON;

WITH Blocking(SPID, SPIDCareBlocheaza, Query, RowNo, LevelRow)
AS
(
SELECT s.SPID,
s.BlockingSPID,
s.Definition,
ROW_NUMBER() OVER(ORDER BY s.SPID),
0 AS LevelRow
FROM (SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
FROM sys.sysprocesses s CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50 ) s
JOIN
(SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
FROM sys.sysprocesses s CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50 ) s1 ON s.SPID = s1.BlockingSPID
WHERE s.BlockingSPID = 0
UNION ALL
SELECT r.SPID,
r.BlockingSPID,
r.Definition,
d.RowNo,
d.LevelRow + 1
FROM (SELECT s.spid, BlockingSPID = s.blocked, DatabaseName = DB_NAME(s.dbid),
s.program_name, s.loginame, ObjectName = OBJECT_NAME(objectid,s.dbid),
Definition = CAST(text AS VARCHAR(MAX))
FROM sys.sysprocesses s CROSS APPLY sys.dm_exec_sql_text (sql_handle)
WHERE s.spid > 50 ) r
JOIN Blocking d ON r.BlockingSPID = d.SPID
WHERE r.BlockingSPID > 0
)
SELECT SPID, SPIDCareBlocheaza, Query, RowNo, LevelRow FROM Blocking
ORDER BY RowNo, LevelRow
END