Handy script for use when looking at blocking

Published On: 2009-03-09By:

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


Contact the Author | Contact DCAC


Globally Recognized Expertise

As Microsoft MVP’s and Partners as well as VMware experts, we are summoned by companies all over the world to fine-tune and problem-solve the most difficult architecture, infrastructure and network challenges.

And sometimes we’re asked to share what we did, at events like Microsoft’s PASS Summit 2015.

Awards & Certifications

Microsoft Partner   Denny Cherry & Associates Consulting LLC BBB Business Review    Microsoft MVP    Microsoft Certified Master VMWare vExpert
INC 5000 Award for 2020    American Business Awards People's Choice    American Business Awards Gold Award    American Business Awards Silver Award    FT Americas’ Fastest Growing Companies 2020   
Best Full-Service Cloud Technology Consulting Company       Insights Sccess Award    Technology Headlines Award    Golden Bridge Gold Award    CIO Review Top 20 Azure Solutions Providers
Share via
Copy link