A while back I was looking at a clients database and we were looking at why the ASPState database was having blocking issues. So I through this script together to show not only the blocked processes, but also the blocking processes, but also include the name of the stored procedure as well as the statement within the stored procedure which was causing the blocking.
[sql]select (select name from sys.dm_exec_sql_text(sql_handle) a join ASPState.sys.all_objects b on a.objectid = b.object_id),
(select substring(text, stmt_start/2,
((case when stmt_end = -1 then
(len(convert(nvarchar(max), text)) * 2)
else
stmt_end
end) – stmt_start) / 2) from sys.dm_exec_sql_text(sql_handle)),
*
from sys.sysprocesses
where (blocked 0 or spid in (select blocked from sys.sysprocesses))
and dbid = db_id(‘ASPState’)[/sql]
You’ll see it is a pretty basic query, but it gets the job done and gives you relevant information about the procedures. Because I’m joining to the sys.all_objects catalog view I have to specify the ASPState database, so if you want to use this you may need to change the ASPState database name to your database name you are looking at (look in the first subquery in the first line of code).
Denny
