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,
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
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