Answered by:

SQL Server stopped responding

Question

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'"

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.

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.

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

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

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