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

Advertisements

Posted on July 22, 2009, in TSQL Tips n Tricks. Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: