[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 516
  • Last Modified:

SQL Server Stops responding randomly until reboot

Hi,
    I have a SQL server 2000 server that completely stops responding at random parts of the day since around 3 weeks and I can only resolve the issue by rebooting.  The server has been running good for about 3 months and now suddently this issue came by.  What I mean by "stops responding" is that any queries from clients result on an ODBC timeout expired or a network general communication failure error.  On the server itself, I can open enterprise manager but it freeze as soon as I try to expand the instance of the server.  The server itself is still running good, I'm able to open other programs and able to remote control it without problems.  

The processors are although running like they are having a hearth attack, running from 0 to 60% every second or so.  
Memory usage is low (around the 300mb) and perfmon is not saying anything relevant on memory nor hard drives queue.  

The error logs are not saying anything related to sql server or hardware problems (nor anything else actually).
Once the server gets rebooted, I see in the sql server logs that the logging completely stops when sql server freezes, it doesn't even log the server shut down.  Also, if I try to get anything out of the perfmon on the sql server instance, any counter returns 1 static value, even on stuff that always move like lock requests per sec.
Nothing has changed before this behavior began on the server nor on the network.
We've changed the memory completely to rule out the possibility of a memory issue.
All diagnostics ran on the server returned nothing relevent.
I've changed the sql server memory allocation from dynamic to static and boosted the server's process priority, no changes.

The server is:
Sql server 2000 SP3 running on a Windows Server 2003 system, Xeon 2.6, 4 gig ram, raid 5 on data, mirror on os.  OS, drivers, firmware and BIOS were updated last week (part of troubleshooting).

I really need help on this one, I've done a lot and need fresh ideas, its a production server!

David
0
davbouchard
Asked:
davbouchard
  • 5
  • 3
1 Solution
 
Atlanta_MikeCommented:
Next time it happens do a DBCC OPENTRAN. If there is an open transaction do a DBCC INPUTBUFFER(spid) to finidout what it is.

 Sounds like you either have a long running query or a transaction being left open.

Other possibilities are high amounts of blocking by a process being run from a job (since it's time specific). Checek you jobs.

0
 
Atlanta_MikeCommented:
utilize sp_who2 to see if there is blocking and excessive locking going on.

If locking check out this article:

http://support.microsoft.com/kb/271509/en-us
0
 
davbouchardAuthor Commented:
Atlanta,
          I'm unable to get into Enterprise manager nor query analyser, so I have no ways of running any of these commands.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Atlanta_MikeCommented:
That is the only way to find out what is going on. Do you not have any DBAs to assist?

If you want to solve the issue you must be able to run these commands.
0
 
davbouchardAuthor Commented:
Atlanta,
           I am the DBA!  This is why I'm posting here, I'm stuck.  When SQL Server freeze, I'm unable to get into enterprise manager (server does not respond) and Query Analyser cannot connect to the database, connection failure again, as the server is not responding.  I have tried leaving a session opened, so I'm already connected when it stops responding, but any queries end up running for an hour without returning anything.
0
 
Atlanta_MikeCommented:
Ahhh... I understand. I thought you were saying you couldn't get to query anayzer period.

The bad thing is you need to catch it pretty quick if it is blocking everything.

Checkout the link I gave you. http://support.microsoft.com/kb/271509/en-us

Run profiler around the time that you have issues. And hopefully you can trap the issue. I know this is difficult.

Also, you should be able to run these DBCC commands from Query analyzer. Make sure you have a query window open with the DBCC commands ready when it happens.

These issues are sometimes a bear to find. I've seen issue where the app developer began a transaction, but never committed it. I've seen queries that run for 45 min blocking everyone... pitiful design.

0
 
davbouchardAuthor Commented:
Well,
       I used the link http://support.microsoft.com/kb/271509/en-us and digged a little deeper using their stored procedure and osql (that was really the only way to get anything out of the db, as perfmon wasn't outputting anything) to finally find that the Ghost Cleanup server process was my issue.  I've installed sp4 and truncated completely my transaction log, ran a scan disk on the data drive to find a couple bad clusters on the drive and finally removed the "auto shrink" option on the DB and now everything is running smooth well for the last 3 days.  Thanks for the link Atlanta!  Now I'll try to put the auto shrink back on to see if its still gonna give me hard times or not...

David
0
 
Atlanta_MikeCommented:
Excellent!! Glad AI was able to help.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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