Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Best way to find SQL Locks in SQL Server 2008

Tags:

sql

locking

What is the best way to find the SQL locks along wih the user associated with that lock in SQL Server 2008?

like image 385
Gaurav Mittal Avatar asked Aug 21 '11 16:08

Gaurav Mittal


People also ask

How can we check locks in SQL Server?

To obtain information about locks in the SQL Server Database Engine, use the sys. dm_tran_locks dynamic management view.

How do I find blocked queries in SQL Server?

In SQL Server Management Studio (SSMS) Object Explorer, right-click the top-level server object, expand Reports, expand Standard Reports, and then select Activity - All Blocking Transactions. This report shows current transactions at the head of a blocking chain.


2 Answers

select * from sys.dm_tran_locks will list all current locks, granted or pending, along with the requesting session id. select * from sys.dm_exec_sessions will list all current sessions, including the client host and login name. But going this way is very seldom what you want. For a more digestible form, use the Activity Monitor and watch the blocking as reported there.

like image 64
Remus Rusanu Avatar answered Sep 29 '22 04:09

Remus Rusanu


Run this against the master db:

SELECT spid,blocked,program_name,loginame,hostname,lastwaittype,* 
FROM master.dbo.SysProcesses
WHERE blocked <> 0
like image 30
Carlos Avatar answered Sep 29 '22 04:09

Carlos