Hi,
I seem to have a problem that might be caused by undetected deadlocks on my system which is causing regular minute-long freezes. The way to resolve the problem is to reboot the SQL Server box, but this is inconvenient as it is a production system, and I was wondering whether any forum users might have any insight into this problem Details below:
We have a main and a standby SQL Server 2000 enterprise edition installation (service pack 3) running on Windows 2000 service pack 4.
It has a service application (the "Eeams" server) with 21 threads connecting to it, one of these being called the "chain" thread (just a name for it) and the other 20 being called "subject" threads (again just a name).
When a job comes in to the "Eeams" service it is processed by one of the subject threads, calling a stored procedure called "process_subject". It updates the "subject" table (and some others) and adds an entry to the "chain" table.
Then asynchronously the Eeams service repeatedly calls a single "process_chain" stored procedure which processes the "chain" table entry, reads data from the "subject" table, adds other table entries, and deletes the "chain" table entry.
These stored procedures normally take under one second to run. However the Eeams server has a 60 second timeout after which it will cancel them.
For months this arrangement works OK. Occasionally there are deadlocks between the two processes, but these are handled correctly.
However every few months the service gets into a mode where every 5 minutes or so, intermittently, the system will sieze up with both of the stored procedures running, and after 60 seconds the Eeams service cancels both procedures, and the system returns to normal, processes some more items, then siezes up again.
Running a SQL Profiler trace shows that the queries do indeed take 60 seconds before being cancelled. It also shows other shorter queries from other threads running OK and taking under a second to complete.
Its as if there is a deadlock between the two processes that is not being detected. Is this a known problem with SQL Server
Although the system is busy, with typically 2 to 6 "subject" threads running the
SQL Enterprise Manager "Current Activity" shows the "chain" thread is blocking the five "subject" threads.
The %CPU and memory are all within acceptable limits, and there is no excessive pageing.
Thanks Microsoft people.

Un-detected deadlocks?
Beltone
The scenario you describe sounds like a standard blocking issue. If there is one spid at the head of the chain, and that spid isn't blocked then presumably it's making progress, even if slowly.
There is a great article on resolving this kind of issue here: http://support.microsoft.com/kb/271509/en-us