What is the best way to find the SQL locks along wih the user associated with that lock in SQL Server 2008?
To obtain information about locks in the SQL Server Database Engine, use the sys. dm_tran_locks dynamic management view.
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.
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.
Run this against the master db:
SELECT spid,blocked,program_name,loginame,hostname,lastwaittype,*
FROM master.dbo.SysProcesses
WHERE blocked <> 0
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With