May 26, 2010 at 12:41 am
Hi,
Last week because of high number of lock SQL 2008 became unresponsive for 4 minutes firing the message below.we had to restart the SQL server. what does that message mean? if we hadn't stopped the server would it be available sometime later.
New queries assigned to process on Node 0 have not been picked up by a worker thread in the last 60 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: 97%.
Also all the sessions were reset, as i understand from the err message? is there a fix for this problem?
The client was unable to reuse a session with SPID 1853, which had been reset for connection pooling. The failure ID is 29. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
thanks.
Regards,
MShenel
May 26, 2010 at 2:01 am
Hi ,
It seems like your database became unresponsive at that time because the CPU utilisation % was very low. But After analysing the Error Msg I would need a few more information about your Enviornment.
1. SQL SERVER 2008 Version Details.
2. What are the maximum number of connections at your database in Peak Hours.
3. What is the configured value for 'MAX Worker Thread' option . for this you can check using sp_configure .
4. Were other users working or every thing became unresponsive?
Pls. share the above info to proceed futher and analyse this issue in details
May 26, 2010 at 2:11 am
Hi,
the details are:
version:SQL Server 2008 SP1 CU7 version 10.0.2757
connection:max number of connection 3000-3500
thread count:max worker thread is 400
no connections could be done even using DAC.
Regards,
MShenel
May 26, 2010 at 2:31 am
Yes , this is exactly what i suspected.
See as You mentioned Your Total number of Connections are >3000. But Max worked Threads is set to 400 Only. I would suggest you to immediately set the Max worked Thread property to at least 4000.
sp_configure 'show Advanced options' ,1
go
sp_configure 'Max worker Threads', 4000
go
Reconfigure with Override
'MAX WORKER THREADS' is basically meant for Keeping the Thread Readily Available in a pool for use for any new Request made to Database.
It is also Recommened to set its value near to the maximum number of connections you have at database at any point in time. (considering the Peak hours)
After setting this option you have to restart your Database Services to get this changed reflected in Server Configuration.
I would suggest to change the setting right now and restart the services whenever you plan some downtime window this will ensure 100% compliance and uptime.
Note:
When all worker threads are active with long running queries, SQL Server may appear unresponsive until a worker thread completes and becomes available. Though not a defect, this can sometimes be undesirable. If a process appears to be unresponsive and no new queries can be processed, then connect to SQL Server using the dedicated administrator connection (DAC), and kill the process. To prevent this, increase the number of max worker threads.
For further information on this pls read out the below link.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply