SQL Server Performance

Connection Pooling Issue Error 18056

Discussion in 'ALL SQL SERVER QUESTIONS' started by farooqkhan4u, Dec 14, 2011.

  1. farooqkhan4u New Member



    Hi All,

    I am getting a lot of messages like below where the connections are lost, Can someone please help me out here..

    18056 1033 20 1 The client was unable to reuse a session with SPID %d, which had been reset for connection pooling. The failure ID is %d. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

    Server Configuration:Microsoft SQL Server 2008 R2 (RTM) - 10.50.1777.0 (X64) Apr 8 2011 14:16:38 Copyright (c) Microsoft Corporation Web Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

    Here are the output of few queries which can be useful in determining the root cause,

    select cpu_count, hyperthread_ratio, max_workers_count, scheduler_count
    from sys.dm_os_sys_info

    cpu_count hyperthread_ratio max_workers_count scheduler_count
    ----------- ----------------- ----------------- ---------------
    12 6 640 12

    select *
    from sys.dm_os_wait_stats
    where wait_type = 'THREADPOOL'

    wait_type waiting_tasks_count wait_time_ms max_wait_time_ms signal_wait_time_ms
    ------------------------------------------------------------ -------------------- -------------------- -------------------- --------------------
    THREADPOOL 4528 8606 42 1

    select COUNT(*)
    from sys.dm_os_workers
    -----------
    159


    select COUNT(*)
    from sys.dm_os_tasks
    -----------
    41

    One of the output from the below query:
    SELECT CAST(record AS XML) FROM sys.dm_os_ring_buffers
    WHERE ring_buffer_type = 'RING_BUFFER_CONNECTIVITY'

    <Record id="81" type="RING_BUFFER_CONNECTIVITY" time="7159936605">
    <ConnectivityTraceRecord>
    <RecordType>ConnectionClose</RecordType>
    <RecordSource>Tds</RecordSource>
    <Spid>418</Spid>
    <SniConnectionId>B43B767C-9B5E-4336-9BAB-4FBE455448ED</SniConnectionId>
    <SniProvider>7</SniProvider>
    <RemoteHost>10.99.195.187</RemoteHost>
    <RemotePort>57994</RemotePort>
    <LocalHost>10.99.194.110</LocalHost>
    <LocalPort>1433</LocalPort>
    <RecordTime>12/14/2011 17:24:37.114</RecordTime>
    <TdsBuffersInformation>
    <TdsInputBufferError>0</TdsInputBufferError>
    <TdsOutputBufferError>0</TdsOutputBufferError>
    <TdsInputBufferBytes>8</TdsInputBufferBytes>
    </TdsBuffersInformation>
    <TdsDisconnectFlags>
    <PhysicalConnectionIsKilled>0</PhysicalConnectionIsKilled>
    <DisconnectDueToReadError>0</DisconnectDueToReadError>
    <NetworkErrorFoundInInputStream>0</NetworkErrorFoundInInputStream>
    <ErrorFoundBeforeLogin>0</ErrorFoundBeforeLogin>
    <SessionIsKilled>1</SessionIsKilled>
    <NormalDisconnect>0</NormalDisconnect>
    <NormalLogout>0</NormalLogout>
    </TdsDisconnectFlags>
    </ConnectivityTraceRecord>
    <Stack>
    <frame id="0">0X0000000001B825AB</frame>
    <frame id="1">0X0000000001B7F4B6</frame>
    <frame id="2">0X0000000000E3EE79</frame>
    <frame id="3">0X00000000007FB4D8</frame>
    <frame id="4">0X00000000007FBB06</frame>
    <frame id="5">0X00000000007FB94B</frame>
    <frame id="6">0X00000000009461F6</frame>
    <frame id="7">0X0000000000946345</frame>
    <frame id="8">0X0000000000DC5B65</frame>
    <frame id="9">0X0000000000945336</frame>
    <frame id="10">0X00000000746837D7</frame>
    <frame id="11">0X0000000074683894</frame>
    <frame id="12">0X000000007787652D</frame>
    <frame id="13">0X00000000779AC521</frame>
    </Stack>
    </Record>

    Output of "Exec Sp_Configure"
    Name minimum maximum config_value run_value
    access check cache bucket count 0 65536 0 0
    access check cache quota 0 2147483647 0 0
    Ad Hoc Distributed Queries 0 1 0 0
    affinity I/O mask -2147483648 2147483647 0 0
    affinity mask -2147483648 2147483647 0 0
    affinity64 I/O mask -2147483648 2147483647 0 0
    affinity64 mask -2147483648 2147483647 0 0
    Agent XPs 0 1 1 1
    allow updates 0 1 0 0
    awe enabled 0 1 0 0
    blocked process threshold (s) 0 86400 0 0
    c2 audit mode 0 1 0 0
    clr enabled 0 1 0 0
    cost threshold for parallelism 0 32767 5 5
    cross db ownership chaining 0 1 0 0
    cursor threshold -1 2147483647 -1 -1
    Database Mail XPs 0 1 1 1
    default full-text language 0 2147483647 1033 1033
    default language 0 9999 0 0
    default trace enabled 0 1 1 1
    disallow results from triggers 0 1 0 0
    filestream access level 0 2 0 0
    fill factor (%) 0 100 0 0
    ft crawl bandwidth (max) 0 32767 100 100
    ft crawl bandwidth (min) 0 32767 0 0
    ft notify bandwidth (max) 0 32767 100 100
    ft notify bandwidth (min) 0 32767 0 0
    index create memory (KB) 704 2147483647 0 0
    in-doubt xact resolution 0 2 0 0
    lightweight pooling 0 1 0 0
    locks 5000 2147483647 0 0
    max degree of parallelism 0 1024 1 1
    max full-text crawl range 0 256 4 4
    max server memory (MB) 16 2147483647 2147483647 2147483647
    max text repl size (B) -1 2147483647 65536 65536
    max worker threads 128 32767 0 0
    media retention 0 365 0 0
    min memory per query (KB) 512 2147483647 1024 1024
    min server memory (MB) 0 2147483647 4096 4096
    nested triggers 0 1 1 1
    network packet size (B) 512 32767 4096 4096
    Ole Automation Procedures 0 1 0 0
    open objects 0 2147483647 0 0
    optimize for ad hoc workloads 0 1 0 0
    PH timeout (s) 1 3600 60 60
    precompute rank 0 1 0 0
    priority boost 0 1 0 0
    query governor cost limit 0 2147483647 0 0
    query wait (s) -1 2147483647 -1 -1
    recovery interval (min) 0 32767 5 5
    remote access 0 1 1 1
    remote admin connections 0 1 0 0
    remote login timeout (s) 0 2147483647 20 20
    remote proc trans 0 1 0 0
    remote query timeout (s) 0 2147483647 600 600
    Replication XPs 0 1 0 0
    scan for startup procs 0 1 0 0
    server trigger recursion 0 1 1 1
    set working set size 0 1 0 0
    show advanced options 0 1 1 1
    SMO and DMO XPs 0 1 1 1
    SQL Mail XPs 0 1 0 0
    transform noise words 0 1 0 0
    two digit year cutoff 1753 9999 2049 2049
    user connections 0 32767 0 0
    user options 0 32767 0 0
    xp_cmdshell 0 1 0 0


    Thanks a lot in advance
  2. satya Moderator

Share This Page