How can I track down an issue with a locked table, endless query?

by user1447679   Last Updated May 22, 2020 20:06 PM

I have a table that is periodically getting locked in a manner that I don't quite understand. I cannot do the following:

select * from thetable
select count(*) from thetable

There are roughly 2,000 records.

I can do the following:

select top 2000 * from thetable
select * from thetable where ID = etc.

Going backwards to find a new record that perhaps was problematic, incrementing the count in chunks until I can finally reproduce it again:

select top 1500 * from thetable order by ID desc
select top 1550.... etc. and eventually it gets locked and never finishes the query.

Query never finishes... have waited 10 minutes. Only resolution is to restart the service.

The related stored procedure that I thought caused the problem I ran manually (it interacts with this table) and the longest time it took was roughly 45 seconds. This particular procedure goes through many phases and is wrapped in a transaction with a try/catch/rollback/commit. There is no explicit locking set on the procedure.

Any direction or guidance to track down the root issue is greatly appreciated.

Answers 1

You want to run something like 'sp_whoisactive', 'sp_blitzwho', etc while waiting for your select.

With those store procedure, you will get the "blocking" session and you will be able to see what it is running.

Dominique Boucher
Dominique Boucher
May 22, 2020 19:34 PM

Related Questions

Monitoring AlwaysON health for X servers

Updated June 20, 2019 22:06 PM