SQL Server with Mr. Denny

Mar 9 2009   11:00AM GMT

Handy script for use when looking at blocking

Denny Cherry Denny Cherry Profile: Denny Cherry


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.

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)
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')

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).


 Comment on this Post

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when other members comment.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

Share this item with your network: