SQL Server - Tracking blocking

Asked By LeonShargorodsk

06-Sep-07 11:06 AM

What is the best way to track blocked/blocking processes in SQL 2005?
I do not want to use Profiler because it needs to be running all the time.
Thank you in advance for your help,

Leon

06-Sep-07 11:18 AM

I use a modified version of sp_who which excludes all sleeping tasks,
so i can easily see which tasks are blocked / blocking.

Then you have to be quick to dbcc inputbuffer and sp_lock to find out
which procedures/sql and tables are blocking.

Dave

06-Sep-07 11:23 AM

You might want to check out event notifications. You should be able to have a notification on the
blocked process report event.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

06-Sep-07 11:32 AM

Leon Shargorodsky,

How to monitor blocking in SQL Server 2005 and in SQL Server 2000
http://support.microsoft.com/kb/271509

Transactions and Locking
http://www.microsoft.com/technet/scriptcenter/scripts/sql/sql2005/trans/default.mspx?mfr=true

aba_lockinfo
http://www.sommarskog.se/sqlutil/aba_lockinfo.html


AMB