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.