T-SQL: Find Out Who Has Locks In Your Databases

Here are a couple of ways to see who is locking up your SQL Server database.

First, the undocumented system stored procedure sp_who2 gives you a few more columns than the documented sp_who.

And second, this custom query gives a more tailored view of the user and the locks. It also gives you the option of tailoring the sort to your needs.

SELECT DISTINCT
  name AS database_name,
  session_id,
  host_name,
  login_time,
  login_name,
  reads,
  writes
FROM sys.dm_exec_sessions
LEFT OUTER JOIN sys.dm_tran_locks 
  ON sys.dm_exec_sessions.session_id = sys.dm_tran_locks.request_session_id
JOIN sys.databases 
  ON sys.dm_tran_locks.resource_database_id = sys.databases.database_id
WHERE 
  resource_type <> 'DATABASE'
  AND name ='MyDatabase'
ORDER BY 
  name

Hope this helps!

This entry was posted in T-SQL. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *