SQL Server hangs, users receive ODBC timeout

I'm having an ongoing issue, which I've had as long as I can remember, where our SQL Server locks up and causes almost all processes to hang and return an ODBC timeout to the end user.  

I try to track down what is causing the lock, using a Microsoft article that helps identify what is causing the lock but when I go to the SQL Activity monitor to match the SPID that is locked up also.  So I have no way of getting that SPID information to see what's going on.

If I restart the SQL service, everything is back to normal.  We are running SQL 2000 SP3 (business system requirement)

Is there a tool out there that will identify what is causing this?  Or is there a better way to run down what is causing the problem?  What I'm currently doing is identifying the thread, then thread ID, then running a SQL query to match the KPID to the SPID, then at that point I look up the SPID and see what it's doing.

Any suggestions?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> We are running SQL 2000 SP3

Apply SP4 as it is recommended to stay up to date with the latest service packs.

>> Is there a tool out there that will identify what is causing this?  

Use SQL Server Profiler to capture long running queries, queries with High Reads, Writes and CPU's and tune it out. This would improve performance and reduce time of your long running queries which should ideally bring down your timeouts..

If connections are released faster, then you have room for new more connections or requests thereby reducing the overall timeouts..

1) Look for blocked SPIDs
Here is an article that has some an SP/function that you can run in an open query window that can show blocked and blocking SPIDs. The article shows a couple of tweaks to make it compatible with SQL 2000 SP3


2) Check the Windows Event Logs on the SQL server and the SQL 2000 event logs looking for any signs of trouble near the time when the issue starts. It's a bit of  long shot but worth checking.

3) Using MSDTC? Check for hung connections:

select req_transactionUoW as [UoW ID] from syslockinfo where req_spid = -2

4) Try a non-blocking version of sp_who which doesn't even need temp tables.


Sounds like a wild one to debug... good luck, I know it's not easy.


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
jmchristyAuthor Commented:
That's an interesting article phil, not something I've seen before but seems like a nice place to start.  I will give that a try, the locks unfortunately only occur about once every 2-3 months.

I'll create this utility meanwhile, and see what happens!  If I have no luck I will repost and reference this article.

jmchristyAuthor Commented:
I haven't gotten a chance to try the solution at this point, although it is something I haven't tried and appears to be the solution I'm looking for.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.