Access issues with the 'which had been reset for connection pooling.' error.

  • Single Server: @@Version

    Microsoft SQL Server 2012 (SP1) - 11.0.3368.0 (X64)

    May 22 2013 17:10:44

    Copyright (c) Microsoft Corporation

    Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Mem: 72GB

    CPU: 16 cores (2 physical)

    Error in order of desc:

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

    Login failed for user '<<User Name>>'. Reason: Failed to open the database '<<Database>>' configured in the login object while revalidating the login on the connection. [CLIENT: xx.xx.xx.xx]

    The Issue:

    So what happens is that username and database connect to the server 24/7 thousands of times with no problems and then every once in a while these errors occur. The problem is about once a day the errors occur hundreds of times and the application fails because it can't connect. It takes a restart and things go back to normal.

    I know it was a kind of bug in 2008 but this is 2012. I've also done the THREAD checks and sp_who2 and things seem normal. Any ideas??

    P.S.

    I know what failure ID 46 is but the database exists and is available at the time.

    Thanks

  • http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f11537a5-8dc9-40a8-b2e4-17874685b074/sql-server-2008-r2-error-18056-severity-20-state29

    Yes its for error state 29 but 46 is likely going to be from the same sort of resource contention.

    To help understand the state 46 error, this guy could reproduce it by dropping the user from the db.

    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

    As per the first link, what are the top wait stats during or around the error? And what is your MAXDOP and MAX server memory set to?

    select top 10 *

    from sys.dm_os_wait_stats

    where wait_type not in --remove common waits to identify worst bottlenecks

    (

    'KSOURCE_WAKEUP', 'SLEEP_BPOOL_FLUSH', 'BROKER_TASK_STOP',

    'XE_TIMER_EVENT', 'XE_DISPATCHER_WAIT', 'FT_IFTS_SCHEDULER_IDLE_WAIT',

    'SQLTRACE_BUFFER_FLUSH', 'CLR_AUTO_EVENT', 'BROKER_EVENTHANDLER',

    'LAZYWRITER_SLEEP', 'BAD_PAGE_PROCESS', 'BROKER_TRANSMITTER',

    'CHECKPOINT_QUEUE', 'DBMIRROR_EVENTS_QUEUE', 'LAZYWRITER_SLEEP',

    'ONDEMAND_TASK_QUEUE', 'REQUEST_FOR_DEADLOCK_SEARCH', 'LOGMGR_QUEUE',

    'SLEEP_TASK', 'SQLTRACE_BUFFER_FLUSH', 'CLR_MANUAL_EVENT',

    'BROKER_RECEIVE_WAITFOR', 'PREEMPTIVE_OS_GETPROCADDRESS',

    'PREEMPTIVE_OS_AUTHENTICATIONOPS', 'BROKER_TO_FLUSH'

    )

    order by wait_time_ms desc

  • I will get those for you. Max mem has no limit SQL is allowed to have it all. this is a dedicated server.

    Stats: Note it just happened but only a few times it wasn't the time where it does it non-stop but just incase something jumps out at you here you go:

    LCK_M_U169568193823266231167565225

    THREADPOOL42387154409733728539115

    PAGELATCH_EX87256490296094738487217235816

    LCK_M_IU106552545721383091911534

    PAGELATCH_SH49102244109895766442412991937

    LAZYWRITER_SLEEP787889102683121292566171904

    LCK_M_IX49822848317013059812053

    HADR_FILESTREAM_IOMGR_IOCOMPLETION10108351436276159849150

    DIRTY_PAGE_POLL4715045143557910142390

    SQLTRACE_INCREMENTAL_FLUSH_SLEEP1282851435373513767

    THREADPOOL seems high right?

  • Yes threadpool shouldnt be in the top waits. I would recommend setting your Sql max mem to 68GB as per best practise.

    http://sqlserverperformance.wordpress.com/2009/10/29/suggested-max-memory-settings-for-sql-server-20052008/

    Try that first then investigate further.

  • Doing that now qnd ill report back. I did that with our main clusters that have 256gb of ram but not this one. Ill try it and report back monday.

    Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply