Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Our ERP software uses a SQL Server 2000 database. When we run a specific task on the software that used to take few seconds, a 300 seconds delay happens and SQL Server returns the error "Lock Timeout. SQL Server connection timeout".

I tried to identify the problem using SQL Server Profiler, and I could see some ROLLBACK's there, but I had no conclusions.

Is there a better way to identify what query is locking what resource and what query timeouts?

share|improve this question

You can query the master.dbo.sysprocesses and master.dbo.syslocks tables to see what's actually running on the system, and what locks they are taking. This along with sp_who2 can help you figure out what's locking the records, or what blocks are happening.

Are you performing any sort of regular database maintenance on the SQL database? If not, this lack of maintenance can lead to all sorts of performance problems.

share|improve this answer

In Query analyzer run:


Look at the BlkBy columed, which shows what the spid is blocked by

then run:


where spid is an actual number like 51 to see what the blocking spid's last statement was.

share|improve this answer

To build upon what Nick and Denny wrote, put those queries in a SQL Agent job running at whatever interval is best for your situation. Capture the output to a .txt file for follow up. Sounds like it should not have to run that long for you to find the problem source.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.