none
Allowing Multiple Active Recordsets (MARS) is causing sleeping connection leaks

    Question

  • Background....

    Application Type:   C#/.NET Windows Service

    Function:  The application does background processing.   A core process runs every minute and performs a number of queries against a SQL Server database to see if scheduled actions/etc should be performed.    None of the queries are long running or particularly taxing.

    Database:   SQL Server 2012 via the Telerik ORM and one direct call to a stored procedure (in a logging module).

    Issue....

    The problem is that while this service is running, it is leaking a large number  (~2 a minute or process cycle) of connections to the database -- all with a status of 'sleeping'.     So, after running for a day it would often have over 1000 of these zombie connections to the database.

    The database connection, through a Unit of Work that is extensively used/tested in other implementations, is disposed by way of a 'Using' statement in every method.    The one case where a stored procedure is called is disposed in the 'Finally' clause of a Try-Catch statement.  

    I was unable to trace a creation of a 'zombie connection' to a single query or part of the code while debugging.    The zombies were not created when stepping through the code -- only when it was run straight through.   I'd add to this that the zombies also were not consistently created every process cycle.

    Fix....

    Disabling MARS (Multiple Active Recordsets) in the database connection string fixed the issue.   

    Question....

    1)  Why would disabling MARS fix the issue?  

    2)  Is there any other code/configuration change we could apply to fix this?


    TIA for any help or suggestions.  

    Wednesday, January 22, 2014 7:57 PM

All replies

  • This doesn't sound like a connection leak.  A normal connection leak will be cleared by running GC.Collect(); GC.WaitForPendingFinalizers, as leaked connections are just hanging around on the Gen2 heap waiting for the Finalizer to close them.  And the normal symptom is that your connection pool hits its limit and you can't open new connections.

    So try running GC.Collect(); GC.WaitForPendingFinalizers.  If the GC/Finalization doesn't clean your connections up, then the connection objects must be rooted.  A debugger can tell you where.

    Connection Leaks can indeed be challenging to debug.  I have no idea why MARS would affect this.

    I wrote a little utility to help debug connection leaks that I have used on a couple of projects.  It works by "attaching" to the connection, listening to Connection's StateChanged event, and complaining if it's finalized without being closed.  A copy of it is here:

    http://ssbwcf.codeplex.com/SourceControl/latest#SsbTransportChannel/SqlConnectionLifetimeTracker.cs

    David


    David http://blogs.msdn.com/b/dbrowne/


    Wednesday, January 22, 2014 8:10 PM
  • What you are describing is a symptom of not running close() in your code to close the connection from .Net and return it to the connection pool.  This causes a build up of connection. 

    http://msdn.microsoft.com/en-us/library/8xx3tyca(v=vs.110).aspx

    Wednesday, January 22, 2014 8:10 PM