- June 5, 2017
- Posted by: Vikas Patel
- Category: Uncategorized
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/.
OBJECT_NAME(p.OBJECT_ID) AS TableName,
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.