parulprabu (10/3/2013)
SrcName (10/3/2013)
create table #temp(
spid smallint ,
ecid smallint ,
status nchar(30) ,
loginame nchar(128) ,
hostname nchar(128) ,
blk char(5) ,
dbname nchar(128) ,
cmd nchar(16),
request_id int
)
insert into #temp exec sp_who
select * from #temp where blk!=0 order by cast(blk as int) desc
This will not give textdata (Query which query get it blocking)
I have been used your way of getting blocked SPID, read data from DM-TABLE, but i think that is slower compared with sp_who.
I get sql text on this way
declare @sqltext VARBINARY(128)
select @sqltext = sql_handle
FROM sys.sysprocesses
WHERE spid = @BLOCKED_SPID
SELECT TEXT
FROM sys.dm_exec_sql_text(@sqltext)
Dear parulprabu
You may try and tell us opinion