I was working on some code for another project at work today when a I got call from one of the users that I support. They had explained to me that the web application they use to pull reports wasn't working. Knowing the history of this particular application I had a feeling there as a blocked process on the SQL server that was preventing other processes or transactions from running. I wrote this script awhile back, and I thought I would share it. It queries the sys.dm_tran_locks table, then joins the sys.databases, and joins the sys.dm_os_waiting_tasks to see what transaction is causing the block. Before giving the query below are the details about each table we are going to query to get our results.
What is the sys.dm_tran_locks table?
The sys.dm_tran_locks table returns information that is currently active in the lock manager resources on the SQL Server in question. Each row represents as active request to the lock manager for a lock that has been granted or is waiting to be granted.
What is the sys.databases table?
The sys.databases table contains one row per database in the instance of Microsoft SQL server that you are working with.
What is the sys.dm_os_waiting_tasks table?
The sys.dm_os_waiting_tasks table will return information about the wait queue of tasks that are waiting on some resource.
The query to show you what transactions are blocking:
select tl.resource_type, tl.resource_database_id, d.name, tl.request_mode, wt.blocking_session_id
from sys.dm_tran_locks AS tl
inner join sys.databases AS d
on tl.resource_database_id = d.database_id
inner join sys.dm_os_waiting_tasks AS wt
on tl.lock_owner_address = wt.resource_address