Connection pooling reset error

Question

Hello:

 

I am getting following error on my SQL Server 2008 R2 with update patch 7 as mentioned below. SQL Server is in production with no restore activity. same issue has been addresses for SQL Server 2008 with patch http://support.microsoft.com/kb/2543687/en-us?sd=rss&spid=13165

Error:-

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.

Any input is great, if this was resolved beside hotfix.

Thanks

Answer 1

Hi Aditya,

You need to install hot fixes to both SQL Server and windows to resolve the error. See below for more details on this topic.

http://support.microsoft.com/kb/2491214

 

---

--Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 2

These errors happen intermittently and should not be a concern unless you are having a problem with the application.  Are you?

 

Answer 3

The problem is when this error happens almost more than 2k+ users gets disconnected or dropped. so i cannot ignore this error. SQL Server is on patch 7 and Shanker mentioned is patch - 6. so far it has happened at least 2-3 times in this month (MAY)

Thanks

Answer 4

Hi Aditya,

How abt the Windows hot fix? Are you current with them too? If yes, your best bet is open a case with MSFT. If Not, plan accordingly.

---

--Sankar Reddy

Blog: http://SankarReddy.com/
Twitter: http://twitter.com/SankarReddy13/

Answer 5

Hi aaditya2000,

According to the error message, these two articles addressing this type of issue may helpful to you:
http://connect.microsoft.com/SQL/feedback/ViewFeedback.aspx?FeedbackID=468478 and 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.
 

---

Best Regards,
Stephanie Lv

Answer 6

Unfortunately, this error can be caused by many things.  It basically means the SPID is not ready for a new connection, so it is refused. 

One case is if the calling .NET program did not complete the query request entirely and close the connection gracefully, causing the call to "sp_resetconnection".  This leaves the SPID in an "invalid" state and forces it to be closed the next time it the caller tries to use it.

Are there any other errors in the SQL log?  Is the application erroring?  Is there any indication of a performance issue on the SQL Server, page swapping, pausing, etc?

I see this kind of error a lot when the caller timesout on a query.