Error: 17189, Severity: 16, State: 1.

  • We're running 2008 SP2, 8 core, 64 GB, Enterprise Edition with about 200-500 connections. have min/max Memory setting 0/60000 and lock pages in membery.

    Got this error once (haven't seen before):

    "Error: 17189, Severity: 16, State: 1.

    Message

    SQL Server failed with error code 0xc0000000 to spawn a thread to process a new login or connection. Check the SQL Server error log and the Windows event logs for information about possible related problems. [CLIENT: xxx.xxx.xx.xx]"

    Followed by a number of these errors:

    "Error: 18056, Severity: 20, State: 29.

    Message

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

    Followed by a bunch of these:

    "Error: 10982, Severity: 16, State: 1.

    Failed to run resource governor classifier user-defined function. See previous errors in SQL Server error log from session ID 439 for details. Classifier elapsed time: 0 ms. "

    The instance did not restart but caused a number of application timeouts. Googling content of SQLDUMPER_ERRORLOG.log and ERRORLOG at the time of issue did not produce any meaningful results. What could be the issue?

  • Lexa (5/23/2012)


    ...

    Followed by a bunch of these:

    "Error: 10982, Severity: 16, State: 1.

    Failed to run resource governor classifier user-defined function. See previous errors in SQL Server error log from session ID 439 for details. Classifier elapsed time: 0 ms. "

    The instance did not restart but caused a number of application timeouts. Googling content of SQLDUMPER_ERRORLOG.log and ERRORLOG at the time of issue did not produce any meaningful results. What could be the issue?

    Sounds like you have configured resource governor improperly. You need to fix it.

    When you say Application timeout, do you mean login time out or query timeout?

  • The resource governor error could be a symptom rather than a cause. Have you seen this article in your Googling?

  • Try the below link

    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

    Ur most likely facing a memory issue , check your working set as well as the target memory settings.

    If you are confident u have plenty of memory available then try increasig the user connections property in sp_configure to accept a higher number of concurrent users.

    Also verify if other processes are encountering any wait types or memory grant issues.

    Jayanth Kurup[/url]

  • Suresh B. (5/23/2012)


    Lexa (5/23/2012)


    ...

    Followed by a bunch of these:

    "Error: 10982, Severity: 16, State: 1.

    Failed to run resource governor classifier user-defined function. See previous errors in SQL Server error log from session ID 439 for details. Classifier elapsed time: 0 ms. "

    The instance did not restart but caused a number of application timeouts. Googling content of SQLDUMPER_ERRORLOG.log and ERRORLOG at the time of issue did not produce any meaningful results. What could be the issue?

    Sounds like you have configured resource governor improperly. You need to fix it.

    When you say Application timeout, do you mean login time out or query timeout?

    Someone before my time configured resource governor but I haven't seen this issue in the past 14 months. Yep, application not able to login.

  • HowardW (5/24/2012)


    The resource governor error could be a symptom rather than a cause. Have you seen this article in your Googling?

    Very good article, I have not seen it, thanks for sharing. We did not upgrade to 2008 from 2000 and from what I can tell all settings under sp_configure (below) are as what they supposed to be. If this is a connection pool issue, why did it occur specifically at the time it did? I did not see excessive blocking or unusually high number of connections. And even if we had more connections than usual, according to sp_configure max number of those is 32767 at the time SQL was trying to recycle SPIDs in the range of 200-500. Do not think we were even in the 1000s. So still not sure why it failed to swap new ones.

    nameminimummaximumconfig_valuerun_value

    access check cache bucket count06553600

    access check cache quota0214748364700

    Ad Hoc Distributed Queries0111

    affinity I/O mask-2147483648214748364700

    affinity mask-2147483648214748364700

    affinity64 I/O mask-2147483648214748364700

    affinity64 mask-2147483648214748364700

    Agent XPs0111

    allow updates0100

    awe enabled0100

    backup compression default0111

    blocked process threshold (s)08640000

    c2 audit mode0100

    clr enabled0100

    common criteria compliance enabled0100

    cost threshold for parallelism03276755

    cross db ownership chaining0100

    cursor threshold-12147483647-1-1

    Database Mail XPs0111

    default full-text language0214748364710331033

    default language0999900

    default trace enabled0111

    disallow results from triggers0100

    EKM provider enabled0100

    filestream access level0200

    fill factor (%)010000

    ft crawl bandwidth (max)032767100100

    ft crawl bandwidth (min)03276700

    ft notify bandwidth (max)032767100100

    ft notify bandwidth (min)03276700

    index create memory (KB)704214748364700

    in-doubt xact resolution0200

    lightweight pooling0100

    locks5000214748364700

    max degree of parallelism06444

    max full-text crawl range025644

    max server memory (MB)1621474836476000060000

    max text repl size (B)-121474836476553665536

    max worker threads1283276700

    media retention036500

    min memory per query (KB)512214748364710241024

    min server memory (MB)0214748364700

    nested triggers0111

    network packet size (B)5123276740964096

    Ole Automation Procedures0111

    open objects0214748364700

    optimize for ad hoc workloads0100

    PH timeout (s)136006060

    precompute rank0100

    priority boost0100

    query governor cost limit0214748364700

    query wait (s)-12147483647-1-1

    recovery interval (min)03276700

    remote access0111

    remote admin connections0100

    remote login timeout (s)021474836472020

    remote proc trans0100

    remote query timeout (s)02147483647600600

    Replication XPs0100

    scan for startup procs0100

    server trigger recursion0111

    set working set size0100

    show advanced options0111

    SMO and DMO XPs0111

    SQL Mail XPs0100

    transform noise words0100

    two digit year cutoff1753999920492049

    user connections03276700

    user options03276700

    xp_cmdshell0100

  • Jayanth_Kurup (5/24/2012)


    Try the below link

    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

    Ur most likely facing a memory issue , check your working set as well as the target memory settings.

    If you are confident u have plenty of memory available then try increasig the user connections property in sp_configure to accept a higher number of concurrent users.

    Also verify if other processes are encountering any wait types or memory grant issues.

    Concurrent connection setting is set to 0=unlimited. We do have 64 GB available and cap 60 GB for SQL. Haven't seen memory pressure issues.

  • I know this thread is old but maybe this can help someone:

    The MS Connect item

    points to the KB in question which, in turn, points to the CU download

    I.e. to remove the error and not break the connection, ensure you have a later CU or Service Pack then the referenced one.

    Best is to eliminate whatever causes the 'ATTENTION' signal; in the first place but this hotfix might be a stop gap solution if you are heavily affected.

Viewing 8 posts - 1 through 8 (of 8 total)

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