none
The client was unable to reuse a session with SPID XX

    Question

  • Hello,

     

    We have a recurring problem with one of our MS SQL Servers (MS SQL Server 2005 SP 1).

    The SQL Server serves as a database server for a Web application with approximately 1500 users, the server is dedicated for the DataBase server.

     

    The scenario is that the database servers cpu peaks at 100% and stays there until restart of the SQL Server service.

     

    The error message from the applicatoion log:

     

    Event Type: Error
    Event Source: MSSQLSERVER
    Event Category: (2)
    Event ID: 18056

     

    The client was unable to reuse a session with SPID 94, which had been reset for conection pooling. This error may have been caused by an earlier operation failing. Check the error logs for failed operations immediately before this error message.

     

    SPID varies.

     

    Anybody that knows anything why this error occurs?

     

    Regards

    John

    Friday, May 11, 2007 6:24 AM

All replies

  • it looks like this issue has cropped back up with SQL 2008.

    I am running the patched SQL 2008 and its been lingering ...  It happens when I build a cube(many connections processor utilization high, blah blah blah).

    this was the fix in 2005.  It was patched.


    Someone UP THERE AT MS FIX THIS IN THE NEXT Cumulative update for 2008 please!!!

    Yes im yelling :)

    Its killing my queries dag nabbit.

    http://support.microsoft.com/default.aspx/kb/937745/
    Tuesday, January 13, 2009 5:52 PM
  • Mike,

    Your best bet is to give feedback at the right place, which is here.
    | Sankar Reddy | http://sankarreddy.spaces.live.com/ |
    • Proposed as answer by retracement Sunday, February 27, 2011 3:09 PM
    Tuesday, January 13, 2009 8:48 PM
    Moderator
  • Mike,

    Unless you can provide a reproducible method of this problem, expect it to not be prioritized by Microsoft even with a connect feedback.  The key to getting resolution is to provide a way to reproduce the problem.  If you do that, and you post a feedback connect item on it, provide the link here so that it can be voted on by the community.  Connect items get prioritized by the impact to the community which is gauged by the amount of validation it gets.

    If it were me, I would trace the instance and identify what happens to the spid in question that causes it to be unusable by the connection pool.  This is likely going to be traced back to a problem somewhere else.
    -- Jonathan Kehayias (MCITP) | Please mark answers that solve your problem | http://www.sqlclr.net
    • Proposed as answer by retracement Sunday, February 27, 2011 3:09 PM
    Saturday, January 17, 2009 6:53 AM
    Moderator
  • we're also encountering this issue. We're running a performance test on one of our db and our cpu hit 80% and kept receiving this error
    The client was unable to reuse a session with SPID ###, 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.

    has anyone found out the root cause for this issue and the solution?
    Friday, September 04, 2009 6:00 AM
  • I’m also seeing this issue a few times per day.  Using Enterprise SQL 2008 Build 2723 on Windows 2008 also Enterprise edition.  There are around 200 transactional replication jobs that run continuously.  The CPU averages around 30% but does get up to 100% on a regular basis.

    Thursday, September 17, 2009 3:32 PM
  • Hi all,
     I am also facing the same problem i have SQL server 2005 with sp3 installed (64 bit),OS is windows server 2003 64 bit enperise edition with SP2 , please help

    Tuesday, September 22, 2009 5:36 AM
  • We're seeing the same issue, but only in production.  We can't reproduce it in DEV or TEST, and tracing it with profiler isn't working, due to too much activity for the trace to keep up.  We're on SQL Server 2008 Engterprise SP1, Windows Server 2003 R2 Enterprise.
    Monday, October 05, 2009 12:16 PM
  • We've been trying to figure this out for almost a year now, and still no luck. Seems to never happen when we want it to. Feedback item was created a while ago, https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=376574
    Adzm
    Monday, October 05, 2009 8:25 PM
  • I changed the SQL maximum server memory setting to 5GB.  The server has a total of 8GB.  Restarted the machine and have not seen this error since.

    Tuesday, October 06, 2009 2:30 PM
  • This error manifests itself due to memory pressure on the server. Since you are using connection pooling, you would see this error message more often if the SQL instance is facing a memory crunch. While resetting the connection, memory is required to perform the necessary validations for the re-connect for security reasons. The entire login process is not repeated but nonetheless, some memory is required. This is why increasing MAX SERVER MEMORY helped in your case.
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: Troubleshooting SQL
    Wednesday, October 07, 2009 7:34 AM
  • Yes I think you can "out memory" this error to defeat it.

    I had 32 gig on mine and still threw it.  I had huge activity though contantly day and night(50 million updates, inserts a day).

    It simply wouldnt stop.  It never caused and issue would just continually throw the error now and then.

    Patching wouldnt stop it etc etc.  I left that position so who knows what happened, I am sure its still throwing it.

    Tuesday, October 27, 2009 3:36 AM
  • AND as expected, we are now receiving this in prod. is this really just a memory pressure issue?
    Wednesday, May 12, 2010 6:09 AM
  • AND as expected, we are now receiving this in prod. is this really just a memory pressure issue?


    This sounds like a golden oldie!  Never seen it myself, but it sounds interesting.  Memory pressure, CPU utilization, memory leaks ... what if you track down the cause of your high CPU utilization and relieve that?  Anytime you get sustained CPU utilization over about 50%, you're cruisin' for a bruisin' anyway.

    And because it's my favorite topic of the month so I apply it to all questions, what is your MAXDOP setting, and how many processors on your server?

    Josh

     

    Wednesday, May 12, 2010 8:13 AM
  • We relieved cpu utilization and it still didn't stop it(stopped the cube building/querying).  Memory was always pegged due to activity.

    It was like it was connection thrashing(I know it was some bad .net code) but still was kind of a strage occurence.

    I was seeing 200k + connections spiking (this was enterprise of course, sql 2008)

    I also didn't think this was a "harmful" error although it looks bad.  I never got complaints of missing data.

    I played a little with the MAXDOP, increasing them(per best practice). This was a 64 bit system, Intel.

    Did nothing.

    Processors was 2 (quad cores) with 32 gig of ram.  We were moving 5 gig+ in disk a day(24*7)

    It would always start out fine, then gradually over a day it starting popping back up. Kicking one out occasionally.

    Wednesday, May 12, 2010 12:32 PM
  • It depends on the state message reported for the 18056 error. This error is received while reusing a pooled connection. If it is reported with a state 29, then under certain conditions, it can be ignored. Refer http://blogs.msdn.com/psssql/archive/2010/05/05/error-18056-can-be-unwanted-noise-in-certain-scenarios.aspx

     


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Friday, May 14, 2010 3:41 AM
  • We relieved cpu utilization and it still didn't stop it(stopped the cube building/querying).  Memory was always pegged due to activity.

    It was like it was connection thrashing(I know it was some bad .net code) but still was kind of a strage occurence.

    I was seeing 200k + connections spiking (this was enterprise of course, sql 2008)

    I also didn't think this was a "harmful" error although it looks bad.  I never got complaints of missing data.

    I played a little with the MAXDOP, increasing them(per best practice). This was a 64 bit system, Intel.

    Did nothing.

    Processors was 2 (quad cores) with 32 gig of ram.  We were moving 5 gig+ in disk a day(24*7)

    It would always start out fine, then gradually over a day it starting popping back up. Kicking one out occasionally.


    200k connections???

    How much of the 32gb was allocated to SQL Server?  Couldn't be a matter of overallocating and then getting paged to death, could it?  Haven't seen that myself recently, but maybe it could manifest by CPU-bound stuff thrashing buffers.  This is the only instance on the box?

    Josh

     

    Friday, May 14, 2010 5:08 AM
  • system is running on a 64 bit 8-proccesor server. maxdop is set to 0. I scheduled a trace to try and capture the transaction and dial down which one is causing the havoc. but trace didn't give anything conclusive.  ON the same day that the error occured again, the Server RAM hit a critical warning on the hardware side and had to be replaced. I'm hoping it's "part" of the cause. Planning to capture everything I can when the issue hits again and log a case to microsoft.

    @Amit Banerjee

    "It depends on the state message reported for the 18056 error. This error is received while reusing a pooled connection. If it is reported with a state 29, then under certain conditions, it can be ignored. Refer http://blogs.msdn.com/psssql/archive/2010/05/05/error-18056-can-be-unwanted-noise-in-certain-scenarios.aspx"

    how about state 23? for our system it can't be ignored since its causing a performance degradation when we receive the warnings. 

    Wednesday, May 19, 2010 2:46 AM
  • Are you getting any other errors apart from the the state 23 error prior or post in the SQL Errorlogs?
    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: www.twitter.com/banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Wednesday, May 19, 2010 2:23 PM
  • Same error already for 2 month. Microsoft give my money back!
    Tuesday, June 01, 2010 5:10 PM
  • You sure its causing you a problem?  It was just an annoyance for me.

    Check your connection count.  You'll probably find some bad processes out there spawning connections.

    The one guy complained of 200k connection.  I saw something similar.

    Find the bad code...

    Tuesday, June 01, 2010 5:30 PM
  • I have only local connections. MAX around 20.

    CPU is free.

    Enought MEMORY.

    I have no big queries.

    Middle queries are cached for hours.

    I do not have even 2k clients connected to server.

    I do 1-10 insert per minute.

     

    But for 2 months i receive this bug each day.

    Each day server stops works.

    Tuesday, June 01, 2010 6:15 PM
  • If you are facing a performance degradation along with these errors, then you need to collect diagnostic data and find out what is the bottleneck that you are experiencing during that time. Could you run the initial diagnostic script on the server during a peak period and check if it reports any warnings?

    Reference: http://troubleshootingsql.wordpress.com/2010/01/21/initial-data-collection-script/


    This posting is provided "AS IS" with no warranties, and confers no rights.
    My Blog: http://troubleshootingsql.wordpress.com
    Twitter: @banerjeeamit
    SQL Server FAQ Blog on MSDN: http://blogs.msdn.com/sqlserverfaq
    Wednesday, June 02, 2010 1:56 PM
  • Hey. I have no perfomance degradation. Avarage CPU usage around 10% on 1GHz CPU (VPS).

     

     

    Thursday, June 03, 2010 8:07 AM
  • Microsoft has released CU today in order to address this issue. Here is link http://support.microsoft.com/kb/2543687


    Thanks, Mohan Kumar - www.sqlvillage.com -- Please mark the post as answered if it answers your question.
    Wednesday, May 18, 2011 10:03 PM
  • wow that took a while...
    Tuesday, May 31, 2011 5:55 PM