Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Hotfix for SQL Server 2008/2008 R2 Periodically Does Not Accept Connections Bug

Microsoft has released a hotfix for a very frustrating issue that plagued me for a couple of years at NewsGator.  I recently became reacquainted with this old problem with Error: 18056, Severity: 20, State: 29. Basically what happens is that a SQL Server 2008 or 2008 R2 database server that is under  absolutely no CPU or memory stress suddenly stops accepting connections from your application and web servers. You will get a number of errors in the SQL Server error log, like you see below:

Date  11/18/2011 8:42:40 PM
Log  SQL Server (Current – 11/18/2011 9:00:00 PM)

Source  spid81

Message
Error: 18056, Severity: 20, State: 29.

Date  11/18/2011 8:42:40 PM
Log  SQL Server (Current – 11/18/2011 9:00:00 PM)

Source  spid81

Message
The client was unable to reuse a session with SPID 81, 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.

While this is going on, no middle-tier servers can connect to the SQL Server instance in question. Quite often, you as the DBA will not be able to make a new connection to the server in question either, using SSMS.  Your CPU utilization will go down to zero, with seemingly no activity happening, and the SQL Server Service will continue to run just fine, with no cluster failover or database mirroring failover being triggered. Essentially, your database instance seems to be pouting and not talking to anyone, like an unruly two-year old…  Usually, this problem clears itself up with no intervention within anywhere from one to ten minutes, but occasionally it requires restarting the SQL Server Service to resolve.

We used to see this issue periodically at NewsGator, starting back in 2009 on SQL Server 2008. There was no change or improvement as we moved to SQL Server 2008 R2. I had previously filed a couple of Connect items about it, opened a CSS case, etc., with no final resolution from Microsoft.  Here are some Connect items that describe the issue in more detail:

SQL Server 2008 Periodically Does Not Accept Connections

SQL Server 2008 SP1 CU6 Periodically Does Not Accept Connections  

SQL Server 2008 R2 Does Not Accept Connections

Increasing the default MaxWorkerThreads instance configuration setting seemed to mitigate the issue somewhat, when I first started seeing the issue.  Another change that seemed to help reduce the frequency of the issue was lowering the MaxServerMemory instance configuration setting by a few GB lower than you would otherwise have it set at. Lots of people I know and respect in the SQL Server community have also run into this over the last couple of years. Bob Dorr talked about this back in August 2010 and  Microsoft Escalation Engineer named Tejas Shah talked about it in May 2010.

After this background and history, it seems that the hotfix that I linked to in the first sentence of this blog post corrects the issue. I have not been able to deploy the fix yet on my particular production server where I recently saw the problem, but a good friend of mine from Microsoft has told me that one of his largest customers recently deployed the fix, and they have not seen the issue reoccur since then. According to the KB article for the fix, it is included in these Cumulative Updates:

SQL Server 2008 R2 RTM CU9 (10.50.1804)

SQL Server 2008 R2 SP1 CU2  (10.50.2772)

SQL Server 2008 SP2 CU5  (10.00.4316)

SQL Server 2008 SP3 CU1  (10.00.5766)

The relevant fix does show up in the fix-list for the SQL Server 2008 R2 RTM CU9 Cumulative Update, but not in the fix-list for SQL Server 2008 R2 SP1 CU2 or for SQL Server 2008 R2 SP1 CU3. I have been assured by someone else at Microsoft (who is in a position to know), that the fix is in the SQL Server 2008 SP1 branch, even though it is not explicitly listed in the fix-list.  If you have been running into this issue, I would suggest that you make plans to get a Cumulative Update that is new enough to include the fix as soon as possible. If you are at an organization that does not believe in deploying Cumulative Updates, you might have to make an exception in this case. I would love to hear from anyone else who has been seeing this problem themselves. Thanks for reading!


Filed under: SQL Server 2008, SQL Server 2008 R2 Tagged: Cumulative Updates

Comments

Posted by Glenn Berry on 28 November 2011

I spotted a couple of typos in my original blog post, but unfortunately, the syndication to SQLServerCentral does not always seem to pick up changes from the original blog post.

The main important typo is in the last paragraph, where it should say: "that the fix is in the SQL Server 2008 R2 SP1 branch, even though it is not explicitly listed in the fix-list"

Posted by LuchoF on 28 December 2011

Hi, thanks for interesting post! I got actually this issue but I haven't seen the error 18056 neither this message "The client was unable to reuse a session with SPID 81" but I will roll out the CU9 in my group of SQL 2008 R2 servers first which I'm running all in CU4!

Thanks

Lucho

Posted by AnupamM on 4 January 2012

I've installed CU3 and I've still got this problem. Sent the logs etc to microsoft last night. Let's see what they come back with!

anupam

Posted by xia.wu.1983 on 22 August 2012

facing this issue now on SQL 2008 R2 SP1. Raised a case with Microsoft. see how it goes.

Posted by Faiz-397159 on 13 September 2012

I also see the issue on (10.0.5766.0)

ate 9/13/2012 10:49:30 AM

Log SQL Server (Current - 9/13/2012 10:49:00 AM)

Source spid122

Message

The client was unable to reuse a session with SPID 122, 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.

Posted by Yogesh Ranade on 4 October 2012

I am running Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) enterprise edition. Can someone confirm if CU2 fixes this issue?

Posted by Terrie on 9 November 2012

Does anyone know if this was ever fixed?

Leave a Comment

Please register or log in to leave a comment.