Sdílet prostřednictvím

How It Works: Error 18056 - The client was unable to reuse a session with SPID ##, which had been reset for connection pooling

  • Článek

This message has come across my desk a couple of times in the last week and when that happens I like to produce blog content.  

The error is when you are trying to use a pooled connection and the reset of the connection state encounters an error.   Additional details are often logged in the SQL Server error log but the 'failure ID' is the key to understanding where to go next.

Event ID: 18056

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

Map the failure ID to the following (SQL 2008 and SQL 2008 R2 failure id states)

        Default = 1,

        GetLogin1, 2

        UnprotectMem1, 3

        UnprotectMem2, 4

        GetLogin2, 5

        LoginType, 6

        LoginDisabled, 7

        PasswordNotMatch, 8

        BadPassword, 9

        BadResult, 10

        CheckSrvAccess1, 11

        CheckSrvAccess2, 12

 

        LoginSrvPaused, 13

        LoginType, 14

        LoginSwitchDb, 15

        LoginSessDb, 16

        LoginSessLang, 17

        LoginChangePwd, 18

        LoginUnprotectMem, 19

 

        RedoLoginTrace, 20

        RedoLoginPause, 21

        RedoLoginInitSec, 22

        RedoLoginAccessCheck, 23

        RedoLoginSwitchDb, 24

        RedoLoginUserInst, 25

        RedoLoginAttachDb, 26

        RedoLoginSessDb, 27

        RedoLoginSessLang, 28

        RedoLoginException, 29 (Kind of generic but you can use dm_os_ring_buffers to help track down the source and perhaps -y)

 

        ReauthLoginTrace, 30

        ReauthLoginPause, 31

        ReauthLoginInitSec, 32

        ReauthLoginAccessCheck, 33

        ReauthLoginSwitchDb, 34

        ReauthLoginException, 35

Login assignments from master

        LoginSessDb_GetDbNameAndSetItemDomain, 36

        LoginSessDb_IsNonShareLoginAllowed, 37

        LoginSessDb_UseDbExplicit, 38

        LoginSessDb_GetDbNameFromPath, 39

        LoginSessDb_UseDbImplicit, 40 (I can cause this by changing the default database for the login at the server)

        LoginSessDb_StoreDbColl, 41

        LoginSessDb_SameDbColl, 42

        LoginSessDb_SendLogShippingEnvChange, 43

 

                                Connection string values

 

        RedoLoginSessDb_GetDbNameAndSetItemDomain, 44

        RedoLoginSessDb_IsNonShareLoginAllowed, 45

        RedoLoginSessDb_UseDbExplicit, 46 (Data specificed in the connection string Database=XYX no longer exists)

        RedoLoginSessDb_GetDbNameFromPath, 47

        RedoLoginSessDb_UseDbImplicit, 48

        RedoLoginSessDb_StoreDbColl, 49

        RedoLoginSessDb_SameDbColl, 50

        RedoLoginSessDb_SendLogShippingEnvChange, 51  

  

                                Common Windows API calls

 

        ImpersonateClient, 52

        RevertToSelf, 53

        GetTokenInfo, 54

        DuplicateToken, 55

        RetryProcessToken, 56

        inChangePwdErr, 57

        WinAuthOnlyErr, 58

Error: 18056 Severity: 20 State: 46.

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

State 46 = x_elfRedoLoginSessDb_UseDbExplicit = 0n46

 

There is only one place in the code (We are simply trying to execute a usedb and getting a failure.) that sets this state and it is after we have printed the message 4060 to the client that we could not open the database or the user does not have permissions to the database. Since there are not messages about a database going offline or being recovered and this connection as already established – “Would there have been any permission changes at this time to prevent this login from accessing the database?”

 

I tried this with a test application.

 

Connection pool using database dbTest

User RDORRTest with default database dbTest

 

When I drop the user in the database dbTest the client starts getting the errors as I expected to see.

 

07/28/10 07:56:45.391 [0x00001E5C] SQLState: 28000, Native Error: 18456 [Microsoft][SQL Server Native Client 10.0][SQL Server]Login failed for user 'RDORRTest'.

07/28/10 07:56:45.410 [0x00001E5C] SQLState: 42000, Native Error: 4064 [Microsoft][SQL Server Native Client 10.0][SQL Server]Cannot open user default database. Login failed.

 

My SQL Server error log shows

 

2010-07-28 08:02:40.41 Logon Error: 18456, Severity: 14, State: 50.

2010-07-28 08:02:40.41 Logon Login failed for user 'RDORRTest'. Reason: Current collation did not match the database's collation during connection reset.

2010-07-28 08:02:40.41 spid53 Error: 18056, Severity: 20, State: 50.

2010-07-28 08:02:40.41 spid53 The client was unable to reuse a session with SPID 53, which had been reset for connection pooling. The failure ID is 50. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

 

I password change for the login at the server generated state 8.

If I rename the database I don’t get any information about the rename in the error log and I start getting connection failures.

 

All my attempts to far had been when the login was setup with a default database. However, to get to the 46 condition I had to specify the DATABASE for the connection string.

 

Now all I had to do was drop the user from the database and I get state 46.

 

2010-07-28 08:29:51.61 Logon Error: 18456, Severity: 14, State: 46.

2010-07-28 08:29:51.61 Logon Login failed for user 'RDORRTest'. Reason: Fa iled to open the database configured in the login object while revalidating the login on the connection. [CLIENT: 65.53.66.207]

 

Added the user back and I no longer get the error and the connections continue their work.

Bob Dorr - Principal SQL Server Escalation Engineer

Comments

  • Anonymous
    August 05, 2010
    I've noticed this error for quiet sometime also, but seems like your testing isn't valid on my environment (Win 2k3 x64 SP2, SQL 2K5 SP3). The Apps design is 3 tier with .net, in their connection string, they did mentioned the database name, but the sql logins that they use to connect to DB, its never change or drop in the user database itself or on the Login level. Once a while in a month, I'll see this error, still I could't figure out whts the problem. My guess is on the connection pool itself which is not giving the correct state..

  • Anonymous
    August 18, 2010
    My testing and documentation here is all based on SQL 2008 for the states and such so it might not apply to your system Harry.

  • Anonymous
    August 24, 2010
    The comment has been removed

  • Anonymous
    October 28, 2010
    When is a fix for this scheduled to be released? I was expecting this in CU3 but it was omitted when I looked through the bug fixes. We run a fairly highly loaded and massive throughput SQL Server on NUMA hardware and this is being noticed by our dotNET application servers something like 10 times a day. This was recently upgraded from SQL2005 which was running under maximum CPU utilisation yet we did not have the connection pooling errors. Thanks

  • Anonymous
    November 07, 2011
    Hello, I'm getting this error also in an environment of SQL Server 2008 R2 SP1, but when I get an error SQL Server stops responding, so I have to do is restart the SQL Server. I have about 1500 users in the environment. I applied for CU3 SQL2008 R2, and added more memory on the server, previously owned 32 GB and now 48GB of memory. Is there any fix for this problem, the current patch level?

  • Anonymous
    December 27, 2011
    Marcos: Have a look at this KB: support.microsoft.com/.../2543687. Even though you have installed SQL Server 2008 R2 SP1, this includes only: "In addition to the fixes that are listed in this article, SQL Server 2008 R2 SP1 contains the hotfixes that were included in Cumulative Update 1 through Cumulative Update 6 for SQL Server 2008 R2. This service pack also includes all the security updates that were released through July 2011." (See support.microsoft.com/.../2528583) However, the actual hotfix seems to have been addressed in Cumulative Update 9 (KB 2543687).

  • Anonymous
    December 31, 2011
    Thank you for posting these details! Can you please provide the login failure states in detail description? As I am able to translate few of them based on the name but remaining / rest are clueless. If you can provide this detail that'll be great help to understand the login failure issue properly. Example: State#  7 --> LoginDisabled  --> Login is disabled hence it is failing. State# 20 --> RedoLoginTrace --> ?? **Most of the state IDs after the 20 is hard to understand. Request to help in this matter. Thanks in advance to help in this matter. Thanks, Milind

  • Anonymous
    April 13, 2012
    "DELAZOUL" you said this is fixed in CU 9 for sql 2008R2...the latest CU is CU 5 for SP1? In this "support.microsoft.com/.../en-us it doesn't really say the problem is fixed. Just says MSFT is working on it...Please provide the appropriate CU

  • Anonymous
    January 01, 2014
    i am seeing this event for The failure ID is 29.  and before i see that event log of A fatal error occurred while reading the input stream from the network. The session will be terminated (input error: 0, output error: 0). so this is an network issue and which might lead to other errors and connection pooling issue

  • Anonymous
    December 10, 2014
    Hi, not sure if still valid but we are getting this error across a range of our SQL Server VM's. Even on version 10.50.4000. Nothing is coming up in sys.dm_os_ring_buffers anywhere near the time that we receive the error (hours apart). Not sure this one is solvable?

  • Anonymous
    November 26, 2018
    The comment has been removed