Vikas Patel     Feb,08 2017

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:

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:

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:

Voila, now our query runs and completes as usually.