MSSQL Table Get Locked and Query Keep Running and Unlock It

Today, we were tuning our long running sql queries and found that the longest query was keep running. We know that sql does some locking so we thought that the table would have acquired a lock but it was not release by the process.

To check if there is any table has lock use the following query:

Error when loading gists from https://gist.github.com/.

SELECT
    OBJECT_NAME(p.OBJECT_ID) AS TableName,
    dtl.resource_type,
    dtl.resource_description
FROM sys.dm_tran_locks dtl
    JOIN sys.partitions p ON dtl.resource_associated_entity_id = p.hobt_id

Abobe query gave us a table names which were present in our query

Now find out the spid (process id) so that we can kill that process manually. Use the following system view:

Error when loading gists from https://gist.github.com/.

select * from sys.dm_exec_requests

Find out the session_id (which is our spid) having status as "suspended". In our case it was 77 but it can be different in your case. Now, we can kill this process by kill commnad as follow:

Error when loading gists from https://gist.github.com/.

kill 77 –replace the number with your spid

Voila, now our query runs and completes as usually.