Wednesday, July 22, 2009

Who acquired the exclusive locks

In an environment where more then one developers are debugging their code, and unfortunately database is same, they usually place different kinds of locks and most of the times these are Exclusive locks which force other developers to wait for the first one to complete his work.

But problem is, Who is acquiring the locks?
Here is a simple query to find out the culprit...

SELECT session_id,host_name,request_mode,last_request_start_time,
resource_type, resource_description
FROM sys.dm_tran_locks INNER JOIN sys.databases ON sys.dm_tran_locks.resource_database_id =sys.databases.database_id
INNER JOIN sys.dm_exec_sessions ON sys.dm_exec_sessions.session_id=sys.dm_tran_locks.request_session_id
WHERE resource_type <> 'DATABASE'
AND request_mode LIKE '%X%'
AND name ='YourDatabaseName'

Now you have the session_id and name of person who is responsible for Exclusive Locks, IF YOU WANT, you can kill his session by following simple query

KILL session_number

Example: KILL 253

No comments:

Post a Comment

All suggestions are welcome