• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 275
  • Last Modified:

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?
0
jmchristy
Asked:
jmchristy
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
0
 
sqlrocksCommented:

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

http://visualstudiomagazine.com/articles/2008/06/01/identify-blocked--sql-processes-quickly.aspx

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.

http://vyaskn.tripod.com/sp_who3.htm

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

-Phil
0
 
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.

Thanks!
0
 
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.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now