SQL 2005 becomes unresponsive Help RRS feed

  • Question

  • I have a very strange issue going on with my SQL 2005 64bit version of SQL. The OS I am running is server 2003 r2 64bit enterprise with 12 gig of ram with dual core Xeons. The server runs fine for a while and then all the cups go up to about 90% for no reason. There is nothing in the SQL logs or the Events Logs. When this happens all of my websites and internal apps become unresponsive. The server has been running perfect for about 6 months and now this. The only software I have added besides SQL is LightSpeed by Quest. My server also Pages what I would say is way to much. The swap file is 16 gig and never gets about 11.5 gig full. I have service pack 1 for SQL installed from the beginning. I also from time to time get a general network error and every thing hangs again. I have switched network cards, cables, and my switch. This still happens from time to time. When this happens the event log gives no errors at all. I am not on a cluster nor am I using database mirroring or log shipping. When my CPU’s max out for no reason I can not stop SQL Service I have to end task on it and than restart the service and than all is great again. The consistency of this issue is random, I can find no pattern. Help

    Saturday, January 27, 2007 6:39 PM

All replies

  • Do you know what service is pegging your CPU?  Is it SQL Server?   Did you look at task manager and see?   Please do so.  I recently installed a print driver and SVCHOST started pegging at 90%CPU and it was a driver problem.
    Monday, January 29, 2007 1:34 AM
  • The first thing I would do, since it sounds like you are running other things on the server besides SQL, is restrict SQL to about 8gb of RAM, so you still have memory for other applications.  That may slow down SQL, but stop the paging.   You might also try restricing the CPUs, at least until you figure out what is causing the issue.

    Also, make sure your SQL version is 9.0.2153.   If not, apply the hot fixes to be current.

    Monday, January 29, 2007 2:21 PM
  • When the server gets into this situation, can you please provide the following? If you cannot establish a new connection to the server at this time, try connecting via the DAC (pass the -A option to sqlcmd.exe)

    1st 100 lines of the errorlog

    dbcc memorystatus

    select * from sys.dm_os_schedulers

    select * from sys.dm_os_ring_buffers where type like '%RING_BUFFER_SCHEDULER_MONITOR%'

    Thanks, Ron D.

    Monday, January 29, 2007 3:20 PM
  • IF the server is unresponse use the DAC (Dedicated Admin Console) to see the process running on SQL server. http://msdn2.microsoft.com/en-us/library/ms189595.aspx fyi.
    Monday, January 29, 2007 3:25 PM
  • As soon as my server acts up again I will try this. Thanks very much.
    Wednesday, January 31, 2007 2:26 PM
  • It looks like it is SQL server that is maxing out my cpu. I have uninstalled LightSpeed and reinstalled just the back up agent so maybe that will help.
    Wednesday, January 31, 2007 2:27 PM
  • I am running version 9.00.1399.06 (x64). SQL server and svchost seems to be what is paging. Most of the pages are soft paging but some times hard paging gets bad. I have set my server to use only 10 gig leaving 2 gig for other things. I will see how long it runs before in hangs again. Thanks
    Wednesday, January 31, 2007 2:31 PM
  • This is the RTM version.  You should download and install Service Pack 1 at least as there were known issues for x64 that were addressed in it.
    Wednesday, January 31, 2007 5:31 PM
  • Hi,

    Well I've been stumbling the same syndroms for a while.
    The Following Issues could help you (they solved my issue)

    1. The case of the Full Text Search Service:
         Check the Full Text Search Services, as it known as memory and I/O Performance killer.
         If must use it, use the Sysadmin command [AT] to limit it's activity to specific CPU.

    2.  I've found that a missing Index caused my performance problem.
         For some and unexplained syndrom, a missing Index caused huge CPU activity for a burst time of 30 Minutes
         and than resumed normal activity (No auto update statistics and indexs)
         So check with SQL Profile and Database Tuneup advisor for Index completion

    3. Check that major activity tables had been splitted to seperate file groups (data excluding index on one group and Index on other)

    4. Activate System Monitor to trace performance counters of memory , Disk I/o to trace hardware compliance problem.


    I hope this issue helped





    Thursday, February 1, 2007 3:05 PM