ADO and SQL connection pooling

• Digs

  SSCertifiable

  Points: 7333

  More actions

  November 5, 2010 at 12:09 pm

  #149293

  REF: http://www.codeproject.com/KB/dotnet/ADONET_ConnectionPooling.aspx

  See under :

  You receive the exception with the message: "A transport-level error has occurred when sending the request to the server. (provider: Shared Memory Provider, error: 0 - Shared Memory Provider: )" in your ASP.NET application with Microsoft SQL Server.

  This occurs when Microsoft SQL Server 2000 (AND 2005) encounters some issues and has to refresh all the connections and ADO.NET still expects the connection from the pool. Basically, it occurs when connection pool gets corrupted. What in turn happens is, ADO.NET thinks that the valid connection exists with the database server, but actually, due to database server getting restarted it has lost all the connections.

  If you are working with .NET 2.0 and Microsoft SQL Server, You can clear a specific connection pool by using the static (shared in Visual Basic .NET) method SqlConnection.ClearPool or clear all of the connection pools in an appdomain by using the SqlConnection.ClearPools method. Both SqlClient and OracleClient implement this functionality.

  MY Question...

  I get the above error on from a shared hosting environment...so what to do, as I wish to maintain the best performance possible...and you need connection pool to achieve that .

  System.Data.SqlClient.SqlConnection.ClearAllPools(); ( ALL )

  System.Data.SqlClient.SqlConnection.ClearPools(); ( relelated to specific connection)

  How should I use the above wisely.

  I have basic code that pulls data into a IDataReader, should I clear pools before I do this..how does this effect multiple requestions from multiple user sessions...

  Or should I run the above in Global server when Application_Ends runs..

  How will clearing the SQL server connection pools effect general connection demands.

  The above seam are usefull to avoid leaving connection string open.

  Please advise...