CSS SQL Server Engineers

This is the official team Web Log for Microsoft Customer Service and Support (CSS) SQL Support. Posts are provided by the CSS SQL Escalation Services

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

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

Rate This
  • Comments 9

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

Leave a Comment
  • Please add 4 and 7 and type the answer here:
  • Post
  • 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..

  • 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.

  • We have been seeing this error message with a failure ID of 46 after times of high server load - even though the database is still accessible & the login still functions. Could it be that the connection pool is overloaded? what would you suggest as a next step in troubleshooting?  thanks

  • 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

  • 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?

  • 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).

  • 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

  • "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

  • 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

Page 1 of 1 (9 items)