SQL Server 2008 SP1 CU6 Periodically Does Not Accept Connections

File Name	Submitted By	Submitted On	File Size
Distinct Errors During Connectivity Issue.docx (restricted)	Glenn A. Berry	3/8/2010	-
SQLError.log (restricted)	Glenn A. Berry	3/8/2010	-

Posted by Microsoft on 11/15/2012 at 6:38 PM

The latest post is not related to this item since the state 51 involves an issue with log shipping partners.

Bob Ward
Microsoft

Posted by Luiz Mercante on 10/26/2012 at 12:18 PM

I'm running Microsoft SQL Server 2008 (SP3) - 10.0.5788.0 (X64) Jun 29 2012 21:00:35 Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) and have the following errors:

Error: 18456, Severity: 14, State: 51
Error: 18056, Severity: 20, State: 51

What should i do about it?

Posted by Microsoft on 5/23/2012 at 11:52 AM

This fix for SQL Server 2008 R2 RTM will be included in CU14 currently scheduled for mid-June. Your options are to wait until that time or upgrade to 2008 R2 SP1 CU6 since the fix is included there. If these options are not possible for you I would encourage you to email me at bobward@microsoft.com to discuss this. I'm especially interested in your comment "...WITH MINUS OF 1 MONTH OF WORK".

I want to remind everyone monitoring this post that this fix is only to remove the state 29 message (and not to disconnect the client) since it only represents the presence of an ATTENTION to the server. You must discover why the ATTENTION is being sent to the server to understand the true nature of your problem.

Thank you,

Bob Ward
Microsoft

Posted by Paulo Salgado on 5/7/2012 at 9:30 AM

Any updates on this, I have the exact same issue with a clients database servers. It generates the same errors as above on one server and my server has the same errors including resource governor statements.

I post my server version here:

product level edition
10.50.1600.1 RTM Data Center Edition (64-bit)

94 GB RAM
DELL Equalogic DataStore
16 CORE CPU
DATABASE SIZE 400 GB
3.000 USERS
NO CLUSTER
DATACENTER SERVER STANDLONE WITH MINUS OF 1 MONTH OF WORK.

Posted by Microsoft on 4/17/2012 at 9:31 AM

Unfortunately, I have found out that the fix did not properly make it into SQL 2008 R2 RTM CU13. The fix is confirmed to be in SQL 2008 R2 SP1 CU6 though.

We will schedule the fix to be put into SQL 2008 R2 RTM CU14. If you have a critical reason why you need the fix sooner on SQL 2008 R2 RTM and cannot upgrade to SP1, then contact CSS to request an immediate fix.

Bob Ward
Microsoft

Posted by Microsoft on 4/17/2012 at 7:09 AM

The problem as first reported in this Connect post is now resolved in SQL Server 2008 R2 with the release of SQL 2008 R2 SP1 CU6 and SQL 2008 R2 CU13. The public KB article fo this issue at http://support.microsoft.com/kb/2543687 has been updated to reflect that the problem is now fixed.

The SQL 2008 versions of this fix will be available in the next CU updates for 2008 which is scheduled for May 2012.

Thank you for your patience in working with us on this issue. Special thanks for Aaron Bertrand and Glenn Berry for pushing us (me) to get this resolved.

Bob Ward
Microsoft

Posted by Microsoft on 4/2/2012 at 11:31 AM

Just to confirm what I said in my last post. Our fix will not address the reason for the ATTENTIONs coming into the server (which this error and state indicate). So if you say you see this error along with issues of not connecting to the server, then there likely is a "server issue" you need to address. Our fix will not address that.

Bob Ward
Microsoft

Posted by RPSAdmin on 4/2/2012 at 10:09 AM

Hi,
I am getting same error on Windows 2008 R2 SP1 and SQL Server 2008 R2 SP1.
Error: 18056, Severity: 20, State: 29.
and folowing error
The client was unable to reuse a session with SPID xx, 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. There are no other error messages.
It happens for 10-15 minutes time period when no new connections can come. I understand as per the response from Bob that it will be fixed in next SP for SQL Server. My question is, we have 4 CPU - 8 core machine and hyper threading is turned on, could this be causing these? Also what should we set the max worker thread value to for 4 CPU and 8Core machine? Will setting this help?

Posted by Microsoft on 3/5/2012 at 9:49 AM

I have confirmed that the fixes we made in KB article 2543687 did not correct the problem as stated in the article. I have now started a process to have the correct fix implemented into the next set of CUs for 2008 R2 SP1 and 2008 R2 RTM which should be around mid-April. After that we will be able to port these changes for CUs for SQL Server 2008 SP2 and SP3. We will not be porting a change for SQL Server 2008 SP1 since that is now officially out of support.

I want to be sure that everyone who reads this post understands that the fix we will be making will:

1) Remove the 18056 state 29 error when an ATTENTION is received when resetting a pooled connection
2) Eliminate disconnecting the client. Instead we will properly handle resetting the connection on the next batch from the client.

This fix does not address the cause for the ATTENTION situation. This may be caused by an application cancelling a query or by a query timeout situation which may be caused by some type of bottleneck on the server side.

If you are seeing 18056 with state 29 (and this is the only thing you see in the ERRORLOG), you should find the original cause of this situation by either debugging the reason for the client sending a cancel or investigating the bottleneck on the server side that may be causing a query timeout.

Bob Ward
Microsoft

Posted by Microsoft on 2/29/2012 at 12:08 PM

The 18056 message is generic in that it indicates a problem during the "reset" of a pooled connection. The fix I am investigating for this connect item is specifically for 18056 where the state and faklure ID = 29 (with nothing else in the ERRORLOG).

Your scenario involves failure ID = 46 and the login failed message above it indicates the problem. This means a pooled oonnection cannot change context to its default database. You need to troubleshoot this problem as though if it were an issue trying to have a normal login accessing its default database.

You can read more about all of the failure ID and states for the 18056 error in this blog post:

http://blogs.msdn.com/b/psssql/archive/2010/08/03/how-it-works-error-18056-the-client-was-unable-to-reuse-a-session-with-spid-which-had-been-reset-for-connection-pooling.aspx

Thank you,

Bob Ward
Microsoft

Posted by Gurbir Singh Rataul on 2/29/2012 at 11:54 AM

Hi guys,

I am getting the same below error in SQL Server 2008 R2 CU-4; Any one knows if this has been fixed any new Cumulative update. What do I need to do to fix it ?
Please help !!!!

Error:
2012-02-29 09:00:52.350 Logon Login failed for user 'XXX'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. [CLIENT: IP]
2012-02-29 09:00:52.350 spid74 Error: 18056, Severity: 20, State: 46.
2012-02-29 09:00:52.350 spid74 The client was unable to reuse a session with SPID 74, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error

Posted by Glenn A. Berry on 2/27/2012 at 3:41 PM

We have an open support case (112020780892595) on this, for an instance running on SQL Server 2008 R2 SP1 CU3. My suspicion is that the fix listed in the KB article was not included in the SQL Server 2008 R2 SP1 branch.

Posted by Microsoft on 2/27/2012 at 11:35 AM

I've re-opened this Connect item based on recent investigation we have done for this issue. Thanks to Aaron Bertrand for pointing out the confusion of this connect item.

My prelim investigation shows that the "update" referred to for this problem is documented in KB article: http://support.microsoft.com/kb/2543687. This article deals specficially with a problem where client applications are timing out when using connection pooling.

Further investigation shows that there may have been an issue with how the changes were made for both SQL Server 2008 and SQL Server 2008 R2 cumulative updates. I am currentling working with our product team to find the correct answer for this. I will post another update once my investigation is complete.

Thank you,

Bob Ward
Microsoft

Posted by Hans E. Molin on 11/23/2011 at 6:48 AM

I'm getting a lot of these on SQL Server 2008 R2 SP1 Enterprise.

Posted by CezaryK on 11/9/2011 at 2:15 AM

Hi! The described problem recently occured in our production environment which is one machine hosting SQL Server 2008R2 Service Pack 1 (version number 10.50.2500). It caused our ETL processes to fail completely.

Errors found in the SQL Server ERRORLOG:

2011-11-09 00:48:04.46 Logon     Error: 18456, Severity: 14, State: 46.
2011-11-09 00:48:04.46 Logon     Login failed for user '***'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. [CLIENT: <local machine>]
2011-11-09 00:48:04.46 spid60     Error: 18056, Severity: 20, State: 46.
2011-11-09 00:48:04.46 spid60     The client was unable to reuse a session with SPID 60, which had been reset for connection pooling. The failure ID is 46. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.
2011-11-09 00:48:04.51 spid90     Error: 18056, Severity: 20, State: 29.
2011-11-09 00:48:04.51 spid90     The client was unable to reuse a session with SPID 90, 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.

Can you confirm that the issue was addresed in latest Service Pack 1 for SQL Server 2008 R2 as this thread is closed with status closed/fixed?

Posted by Omair Aleem on 9/12/2011 at 2:22 PM

I should have added in my post below that I specifically am running SQL Server 2008 SP2 with the latest 10.0.4316 build available for the version we're on.

Posted by Omair Aleem on 9/12/2011 at 2:18 PM

It says the bug is closed/fixed but I dont see a resolution here. What update fixed this? We are running SQL Server 2008 on Windows 2008 R2 and have the latest patches as well. I still continue to see these errors.

Posted by Artem Ervits on 5/28/2011 at 7:36 AM

Getting this error on SQL 2008 R2 CU6, less frequently than before but still...

Posted by aaditya2000 on 5/25/2011 at 8:53 AM

Hello:

My SQL Server 2008 R2 Enterprise Edition is on latest patch 7 and i am getting this error which drops several 1000 users on my Application server. any info about special hotfix for this issue.

Login failed for user Reason: Failed to open the database configured in the login object while revalidating the login on the connection

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

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

Posted by SteveGouin on 4/19/2011 at 6:17 AM

CU#7 has been released yesterday for SQL Server 2008 R2 but i can't find any bug fix related to that issue.
Could you give us more information about why it has not been adressed?

thanks

Posted by Alexey Rokhin on 4/15/2011 at 1:57 AM

Have many errors after moving SQL 2008 R2 RTM from 4 Intel Xeon E7450 (6 cores each) with 128 GB RAM to 4 AMD Opteron 6176 SE (12 cores each) with 256 GB RAM. And minidump was created after first error. Have no CLR objects in databases. Applications use SQL Server authentification.

Error: 18056, Severity: 20, State: 29.
The client was unable to reuse a session with SPID 150, 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.

When update will be released for SQL 2008 R2? Is this update only for CLR GC?

Posted by Microsoft on 3/17/2011 at 9:13 AM

Hi Glenn,

This has been addressed, and should be available in our next update. Thanks for filing the issue!

Cheers,
-Isaac

Posted by redrazorback on 12/10/2010 at 1:57 PM

MS Support confirmed this is a bug with the garbage collector and a fix is in the works. Probably won't release until early 2011.

Posted by redrazorback on 12/10/2010 at 1:54 PM

Found the root cause of our 18056 state 29 errors! Caused by SQLCLR garbage collector running under the sql server process. Our third party SQL DML auditing software used CLR triggers for each table. Occasionally garbage collection would hang up and starve sql server of CPU cycles b/c garbage collection runs at higher priority than sql processes by default. Our 18056 errors generated during these times. Our workaround was to remove the CLR triggers. We still have a handful of other CLR objects installed, but stopped getting 18056 errors after dropping the numerous CLR triggers. So it seems to be a function of the number of CLR objects installed.

Posted by Neeraj K Mittal on 11/25/2010 at 9:56 PM

Same thing is happening to us twice a weekon SQL Server 2008(X64) SP1 on windows 2008 SP2 cluster. Domain account is not authenticated during that duration. CPU utilisation is normal. We have opened the case with MS for resolution. Keeping fingers crossed......

2010-11-26 11:28:30.580 spid302     Error: 18056, Severity: 20, State: 29.
2010-11-26 11:28:30.580 spid302     The client was unable to reuse a session with SPID 302, 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.

2010-11-26 11:28:30.710 spid204     Error: 18056, Severity: 20, State: 29.
2010-11-26 11:28:30.710 spid204     The client was unable to reuse a session with SPID 204, which had been reset for connection pooling.

2010-11-26 11:28:31.720 spid122     Error: 18056, Severity: 20, State: 29.
2010-11-26 11:28:31.720 spid122     The client was unable to reuse a session with SPID 122, which had been reset for connection pooling. The failure ID is 29.

2010-11-26 11:28:34.160 spid352     Error: 18056, Severity: 20, State: 29.
2010-11-26 11:28:34.160 spid352     The client was unable to reuse a session with SPID 352, which had been reset for connection pooling. The failure ID is 29.

2010-11-26 11:28:36.990 spid300     Error: 18056, Severity: 20, State: 29.
2010-11-26 11:28:36.990 spid300     The client was unable to reuse a session with SPID 300, which had been reset for connection pooling. The failure ID is 29.

Posted by Glenn A. Berry on 11/23/2010 at 11:30 AM

No update on the CSS case. We continue to see this issue about once every couple of months. We have migrated to SQL Server 2008 R2 CU4, running on Windows Server 2008 R2, and the issue continues. Raising the MaxWorkerThreads setting a little seems to help mitigate the issue.

A plausible theory I have heard from another MVP who works at very large, high volume SQL Server customer in Europe (who is seeing the same issue) is that SQL Server is having a problem communicating with one of its domain controllers when this happens.

I think it makes sense, since I cannot open a new connection to the database instance using Windows authentication when this is happening, even on SSMS running on the database server itself. We are using WS 2008 R2 for all of our database servers and domain controllers.

Posted by redrazorback on 11/18/2010 at 11:22 AM

edit: Glenn, any update on your CSS case?

Posted by redrazorback on 11/18/2010 at 11:21 AM

getting these 18056 (state 29) and 10982 errors on x64 SQL Server 2008 SP1 CU6 on Windows 2008 R2. In effect, SQL Server pauses processing for 40 - 60 seconds and then returns to normal on its own. We have opened a CSS ticket. Alan, any outcome from your CSS case?

Posted by Robert L Davis on 10/28/2010 at 10:08 AM

I have several servers in Production and nonproduction experiencing this problem. They are all hosting SharePoint 2010. Some are hosting PerformancePoint as well. They are all internal MSFT applicaitons if you'd like to get a look at the actual server.

What I see is many instances of the State 29 error followed by a prolonged period of time where nothing at all is logged in the SQL log. At some point during the period where nothing is getting logged, SharePoint reports one or more errors connecting to the server due to login timeout.

Posted by BoomBoomNM on 6/30/2010 at 6:03 AM

Has Anyone found a resolution to this? I am getting the same error messages Error: 18056, Severity: 20, State: 29 with extreamly low utilization.

Posted by AISADMIN on 5/28/2010 at 4:54 AM

Actually i've noticed something interesting meanwhile. Let me explain and see if it helps in resolution.

This happens with the datalist or listview control and custom data paging. But it works fine with GridView :(

In addition i tested with Telerik controls (RadGrid and RadListView).

Means I am using the VERY SAME code in the NeedDataSource event:

' for the RadListView
Me.RadListView1.DataSource = ClassName.GetCatalogProducts(Me.txtSearch.Text.Trim, RadListView1.CurrentPageIndex + 1, 30)
Me.RadListView1.VirtualItemCount = ClassName.CatalogProductsCount(Me.txtSearch.Text.Trim)

' for the RadGrid
Me.RadGrid1.DataSource = ClassName.GetCatalogProducts(Me.txtSearch.Text.Trim, RadListView1.CurrentPageIndex + 1, 30)
Me.RadGrid1.VirtualItemCount = ClassName.CatalogProductsCount(Me.txtSearch.Text.Trim)

And it works perfectly fine for the RadGrid. It loads data and renders very fast.
However RadListView works only up to 3-4 pager clicks. It works also very fast when you go to page #2, #3 and finally when you click the next page (does not matter the page order) it gets locked up .

Means it is not about the code that i use as obviously it works flawless (with RadGrid).

I tried with standard listview control and custom paging . It behaves the very same. It only allows you click up to 3 different pages. Then it hangs to the end of the session (connection timeout i guess).

Is this sort of Bug?

Thanks

Posted by Alberto Morillo on 5/27/2010 at 6:43 PM

Hello,

Another user is reporting the same issue.

http://social.msdn.microsoft.com/Forums/en-US/sqlsetupandupgrade/thread/c046ea4e-a946-426c-b782-8b4be31e9eb9

Posted by volatiless on 5/26/2010 at 11:11 AM

Same 5 times a day.

2010-05-26 15:35:58.39 spid57     The client was unable to reuse a session with SPID 57, 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.
2010-05-26 15:35:58.39 spid59     Error: 18056, Severity: 20, State: 29.
2010-05-26 15:35:58.39 spid59     The client was unable to reuse a session with SPID 59, 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.
2010-05-26 15:35:58.39 spid58     Error: 18056, Severity: 20, State: 29.
2010-05-26 15:35:58.39 spid58     The client was unable to reuse a session with SPID 58, 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.
2010-05-26 15:35:58.40 spid54     Error: 18056, Severity: 20, State: 29.
2010-05-26 15:35:58.40 spid54     The client was unable to reuse a session with SPID 54, 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.
2010-05-26 15:35:58.40 spid55     Error: 18056, Severity: 20, State: 29.
2010-05-26 15:35:58.40 spid55     The client was unable to reuse a session with SPID 55, 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.

Where i can get my money back?

Posted by daleofcourse on 5/17/2010 at 3:27 AM

I have also been experiencing this issue; though it seems to happen more frequently than others have been describing. We're currently running SQL 2008 on Windows Server R2 running on VMWare, the virtual server is currently limited to 16gb RAM.

The problem only started about a week ago.

Posted by Glenn A. Berry on 5/12/2010 at 7:54 AM

We still have an open CSS case on this issue. I have seen other people still reporting this issue recently.

Posted by Shaun5Stu on 5/6/2010 at 8:44 AM

I'm getting the same error on one of my servers. That server is also seeing bottlenecks accessing the hard drive (>15 seconds to access). This is likely the cause of the problem. See http://blogs.msdn.com/psssql/archive/2010/05/05/error-18056-can-be-unwanted-noise-in-certain-scenarios.aspx for more info.

Posted by Idol-111 on 5/2/2010 at 4:10 PM

I have the similar error like ClennAlanBerry has.
The Error 18056, Severity 20, State 29 occurs and CPU becomes 100 %.
Two notes:
1. it happens only on Windows server R2
2. when Data collection was disable everything works fine.

Posted by SQLDAD on 4/28/2010 at 7:24 AM

Does anyone have an update to this issue? We are experiencing the exact same issue. Last post here was on 3/30/2010. Thanks to all who contribute.

Posted by rnbergren on 3/30/2010 at 9:52 AM

We are having this same exact issue. About once a day the SQL server doesn't accept connections for about 30seconds. Resets and everything is then ok. No failover. Nothing restoring nothing major going on.

Posted by CarlSoto on 3/30/2010 at 9:15 AM

Same issue here too on Windows 2008 x64 SP2 and SQL 2008. Causing blips to 3 different applications. Error is sporadic, every 1 to 3 hours daily. Any suggestions?

Posted by godugan on 3/29/2010 at 8:21 AM

Glenn,

I am seeing these same symptoms on a Windows 2008 x64 SP2 and SQL 2008 Std SP1 CUP3. I believe the issue is dues to memory pressure. We you able to find a solution to this problem?

Posted by Glenn A. Berry on 3/18/2010 at 8:31 AM

Please also see Connect 468478 for more information about this issue, which continues with SQL Server 2008 SP1 CU6, running on Windows Server 2008 R2. We have an open CSS ticket for this, and we have supplied minidump files captured during one of these incidents to CSS.

Other people have recently commented on the old Connect 468478, seeing the same issue.

Posted by Glenn A. Berry on 3/18/2010 at 7:41 AM

We have captured some minidumps from SQL Server while this was happening, and given them to CSS. I am hearing reports from other people on Twitter running into the same issue.

Posted by DBA24hrs Inc on 3/16/2010 at 7:14 AM

Any updates on this, I have the exact same issue with a clients database servers. It generates the same errors as above on one server. The second server has the same errors minus the resource governor statements.

Posted by Glenn A. Berry on 3/11/2010 at 8:20 AM

I have open CSS case# 110031158738615 for this issue.

Posted by Glenn A. Berry on 3/11/2010 at 7:44 AM

The SQL Server instance has not restarted during these occurances, and our mirrored databases have not failed over. The databases are all online during this time. The Error 18056, Severity 20, State 29 seems to be closer to the root cause.

In each case, SQL Server 2008 was running normally, with roughly 1500 connections, and then it stopped accepting client connections. No server restart, no mirroring failover, no databases taken offline.

I will open a CCS case for this issue today.

Posted by Microsoft on 3/10/2010 at 1:23 PM

This error state (46) means the db explicitly specified in the connection string is not accessible. The could be severall reasons for it: the db is not online: it can be in RESTORING state (if it is getting restord from backup),RECOVERING (when SQL Server is starting for example),OFFLINE (if it was taken offline by explicit user's action) etc. You can read more about db states here: ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/b7f1f111-ca73-4a89-b567-a98d64d6ecb3.htm
In you case,my guess is as the problem is getting resolved on its own in several minutes, that the server is starting up and the db is not online yet. Anothe option is, if you use mirror, that it is switching to a mirror.
Let us know if you have more questions.

SQL Server 2008 SP1 CU6 Periodically Does Not Accept Connections by Glenn A. Berry