3

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?

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47

3 Answers3

2

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.

mrdenny
  • 27,174
  • 4
  • 41
  • 69
1

In Query analyzer run:

sp_who2

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

then run: 

DBCC INPUTBUFFER(spid)

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

Nick Kavadias
  • 10,796
  • 7
  • 37
  • 47
1

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.

jl.
  • 1,076
  • 8
  • 10