none
SQL Server stopped responding

    Question

  • Hi

    In the last couple of days our SQL server stopped running, when I say running, the SQL Service was up and running, but we could not access SQL Server through SSMS. Also none of the applications could connect to any of the DB's. After restarting SQL everything went back to normal. This happened 3 times in the last two days at random times. When this happened the first two times the following error occured in the SQL logs:New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 660 seconds. Blocking or long-running queries can contribute to this condition, and may degrade client response time. Use the "max worker threads" configuration option to increase number of allowable threads, or optimize current running queries. SQL Process Utilization: 0%. System Idle: 99%.

    The last time the it happened the following two errors came up:A time-out occurred while waiting for buffer latch type 2.... and "Timeout occurred while waiting for buffer latch: class 'ACCESS_METHODS_DATASET_PARENT'"

    Just a note, we have not yet installed SP1.

    Regards

    Thursday, January 12, 2012 5:20 AM

Answers

  • What you describe is always an "out of resources" issue.  The server is too busy to respond to new requests.  This can be caused by many things.  However, the most common is not setting the "max memory" setting which causes the server to massively page swap.

    Does the SQL Server log indicate any errors like:

    A significant part of sql server process memory has been paged out.

    Also, did you set "lock pages in memory".

    If that is all set correctly, then you need to look at your performance counters on the server to find out what SQL Server is too busy.

     


    Thursday, January 12, 2012 2:27 PM
    Moderator

All replies

  • Similar kind of issue is reported here  http://social.msdn.microsoft.com/Forums/en-US/sqlkjmanageability/thread/1b7afb78-34f2-4d02-93df-c675363710fb
    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Proposed as answer by Sandip Shinde Thursday, January 19, 2012 6:47 AM
    Thursday, January 12, 2012 5:22 AM
  • What are the wait types occuring in your system?
    Thursday, January 12, 2012 5:58 AM
  • Sorry at the time when this happens we can not connect to the SQL at all. I have to restart the SQL, after that the error does not come up again.
    Thursday, January 12, 2012 6:08 AM
  • Hi Manish

    Unfortunatly I can not log onto the SQL when it goes down, also we are using SQL 2008 R2 , not 2005

    Thursday, January 12, 2012 6:11 AM
  • I'd recently worked on the similar problem where 'ACCESS_METHODS_DATASET_PARENT' latch was causing the trouble. This latch is related to the parallelism :- http://msdn.microsoft.com/en-us/library/ms175066.aspx

    For this one, I would recommend to check the MAXDOP setting first before getting into further troubleshooting. : - http://support.microsoft.com/kb/329204

    Also, you could login using DAC connection if SQL is hung and query : -  http://msdn.microsoft.com/en-us/library/ms189595(v=sql.90).aspx

     

    HTH!

     

     


    Kind regards| Harsh Chawla | Personal Blog:- SQL-blogs
    |Team Blog:- Team Blog
    Thursday, January 12, 2012 7:59 AM
  • What you describe is always an "out of resources" issue.  The server is too busy to respond to new requests.  This can be caused by many things.  However, the most common is not setting the "max memory" setting which causes the server to massively page swap.

    Does the SQL Server log indicate any errors like:

    A significant part of sql server process memory has been paged out.

    Also, did you set "lock pages in memory".

    If that is all set correctly, then you need to look at your performance counters on the server to find out what SQL Server is too busy.

     


    Thursday, January 12, 2012 2:27 PM
    Moderator
  • As the side note - you can use dedicated admin connection to access the server that stopped responding

    Edit: Oops, sorry - have not noticed that Harsh already recommended it.


    Thank you!

    My blog: http://aboutsqlserver.com


    Thursday, January 12, 2012 2:35 PM
  • Hi Tom

    No only the two errors I mentioned. What basically happens is that we have an application that connects to the Databases on the, SQL server. It makes a lot of inserts. The app basically have different queues, if you can call it that, for inserting data in different Databases and tables, we start noticing that the queues start building up slowly at first. When I then log onto the DB server, I can not spot anything wrong. Performance monitor shows that the Disks are fine,cpu not even reaching 50%, but the memory usage starts climbing at a rapid speed. At first using SSMS works with no issues,Then after about 10 minutes when I use SSMS to open error logs and so on, the SQL server's performance becomes really slow, up to a point where the SQL "hangs". We then have to restart the SQL.

     

    I did not have lock pages set, but did so now. Will check to see if that makes a difference

    Saturday, January 14, 2012 11:30 PM
  • Hi Tom

     

    So far so good, looks like setting lock pages might have solved the prolem, I am just going to continue to monitor it for a couple of days to confirm that all is in order.

    Monday, January 16, 2012 11:28 AM
  • If you haven't, you also need to set the "Max Memory" setting to something reasonable. 

    Please see:

    http://sqlblog.com/blogs/jonathan_kehayias/archive/2009/08/24/troubleshooting-the-sql-server-memory-leak-or-understanding-sql-server-memory-usage.aspx

    Tuesday, January 17, 2012 2:12 PM
    Moderator
  • I think its thread starvation ....can you increase the max worker threads in SQL Server through sp_configure .I remembered Josh (Community contributor) had issue like this and we fixed it by increasing Max-worker threads ..

     

    Regards

    Abhay


    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Tuesday, January 17, 2012 4:33 PM
  • I hope its a 64 bit application and SQL Server as well ...
    Abhay Chaudhary OCP 9i, MCTS/MCITP (SQL Server 2005, 2008, 2005 BI) ms-abhay.blogspot.com/
    Tuesday, January 17, 2012 4:34 PM
  • The max memory was set...thx. Will look at the maximum worker threads . So far it seems like the server is performing, but once in a while we do get complaints from our developers that a Job that they created failed, we also have an Argcis server that connects to the SQL, and once in a while the app looses connectivity(or so it seems) to the DB. But it might be unrelated........still investigating

    Thursday, January 19, 2012 6:45 AM
  • No

     

    It is a 32 bit app, the 64 bit will be released soon....we hope. Why?

     

    Thursday, January 19, 2012 6:47 AM