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

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

 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.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

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: