Killing SQL Server Process with X Lock on a table

Ever troubled with an X Lock on you table which you desperately want to remove?

I was hit with this problem today. You can do the following in order to remove the lock from your table:

First we find out how many locks are on your table.

SELECT REQUEST_MODE, REQUEST_TYPE, REQUEST_SESSION_ID FROM
sys.dm_tran_locks
WHERE RESOURCE_TYPE = 'OBJECT'
AND RESOURCE_ASSOCIATED_ENTITY_ID =(SELECT OBJECT_iD('TBL_PROD_TQ'))

In the above example, the details of locks on the table “MyTable” are queried. Along with the Lock type, the session responsible for the lock is also displayed.

Now if you find there is an ‘X’ lock on your table. Then using the session ID found as a result of the above query, session can be killed. We assume that our session id is 140.

KILL SESSION_ID (e.g. KILL 140)

If you want to get further details about your session, dm_exec_sessions can be used. To get the details of the session:

SELECT * FROM
sys.dm_exec_sessions
where session_id = 114

To get the IP Address of the party involved in the session, we can use dynamic management view dm_exec_connections:

SELECT * FROM
sys.dm_exec_connections
WHERE SESSION_ID = 114