During recent load testing of our larger sql server databases we ran into some performance issues.  After much research by Microsoft it turned out to be an OS issue, and required a patch.  We were running SQL Server 2008 on Windows 2008 R2.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;979149 

Our errors didn't show themselves exactly as the support document does, but we were seeing lots of sql timeout errors (which we did not see under sql 2005 and windows 2003 for the exact same tests). 

The hotfix appears to have corrected the issues.

The sql error logs contained lots of:

Errors in connection pool - 2010-09-16 19:19:27,Error,2,18056,MSSQLSERVER,{removed}.{removed}.corp,"The client was unable to reuse a session with SPID 437, 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."

 So this a typical exception\timeout side effect. The failure id is important here – id is 29. State 29 occurs when there is an Attention received from the client while the Redo Login code is being executed. So as stated I have seen this associated with other more serious exceptions and way I dealt with it is by flushing connection pool on serious exception error handling (handling deadlock for instance). Flushing pool will have however serious performance effects in ADO.NET and cannot be recommended easily.

If the above error message (note that the state number should reflect 29) is the only message in the SQL Server Errorlog along with no other errors noticed in the environment (connectivity failures to the SQL instance in question, degraded performance, high CPU usage, Out of Memory errors), then this message can be treated as benign and safely ignored.

 ·         Errors on login, perhaps real cause of Redo Login and therefore error above - 2010-09-16 19:19:27,Failure Audit,4,18456,MSSQLSERVER,{removed}.{removed}.corp,Login failed for user '{removed}'. Reason: Failed to open the database configured in the login object while revalidating the login on the connection. [CLIENT: {removed ip}]

 ·         2010-09-16 19:19:27,Error,2,10982,MSSQLSERVER,{removed}.{removed}.corp,Failed to run resource governor classifier user-defined function. See previous errors in SQL Server error log from session ID 2185 for details.  Classifier elapsed time: 1 ms.