SQL Server – How Can I Detect a Locked Table and Endless Query Problem?


I have a table that is regularly locked in a way that I don't fully understand. I can't do the following:

select * from thetable
select count(*) from thetable

There are approximately 2,000 records.

I can do the following:

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

Go backwards to find a new record that may have been problematic and increase the number in pieces until I can finally reproduce it:

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

The query never ends. I waited 10 minutes. The only solution is to restart the service.

The associated stored procedure that I thought was causing the problem that I ran manually (it interacts with this table) and the longest time it took was about 45 seconds. This special procedure goes through many phases and is included in a transaction with a try / catch / rollback / commit. No explicit lock is set for the procedure.

Any instruction or guidance to track down the basic problem is greatly appreciated.