none
Sql Connection pooling issue

    Question

  • I have a problem with SQL connection pooling. Please help me in making sense of the problem. The program with issues is a multi threaded application written to run in .Net framework 1.1. It uses disconnected mode of communication with the dataset. It does not open or close the connection and just execute does a DataAdapter.Fill(Dataset) (DBNetAccess.Database.ExecuteDataSet function) to retrieve the data after setting the select command for the adapter.


    The exception stack trace i'm getting is


       at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction)
       at System.Data.SqlClient.SqlConnection.Open()
       at System.Data.Common.DbDataAdapter.QuietOpen(IDbConnection connection, ConnectionState& originalState)
       at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
       at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet)
       at DBNetAccess.Database.ExecuteDataSet(String queryName, IDataParameter[] parameterArray, COMMANDTYPE commandType)


    I am filling up the requirements that had been asked for in another post


    [1] Client side:

    1. What is the connection string in you app or DSN? Data Source=***;Initial Catalog=***;User Id=***;Password=***;
    2. If client fails to connect, what is the client error messages? Unable to get this information as the issue happened in production.
    3. Is the client remote or local to the SQL server machine? Remote
    4. Can you ping your server? YES
    1. Can you telnet to your SQL Server? YES
    1. What is your client database provider? ADO.NET 1.0 
    2. What is your client application? A .Net 1.1 application.
    3. Is your client computer in the same domain as the Server computer? Same domain
    4. What protocol the client enabled? Where can I get this information.
    5. Do you have aliases configured that match the server name portion of your connection string? No.
    6. Do you select force encryption on server and/or client? No

     

    [2] Server side:

     

    1. What is the MS SQL version? SQL Sever 2000
    2. What is the SKU of MS SQL? Enterprise.
    3. What is the SQL Server Protocol enabled? TCPIP & Named Pipes.
    4. Does the server start successfully? YES 
    5. If SQL Server is a named instance, is the SQL browser enabled? NA
    6. What is the account that the SQL Server is running under? Local System
    7. Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? not applicable
    8. Do you make firewall exception for SQL Browser UDP port 1434? not applicable

    [2a]  Tool Used to Connect

    What tool or Application are you using to connect to SQL Server ? Sql query analyzer

     

    [3] Platform:

    1. What is the OS version? Windows 2003
    2. Do you have third party antivirus, anti-spareware software installed? McAfee VirusScan Enterprise Server v 8.0.0.1039.
    I have collected the performance counters:

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Current # pooled and nonpooled connections
    355
    349
    349
    349
    349
    349
    350

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Current # pooled connections
    355
    349
    349
    349
    349
    349
    350

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Current # connection pools
    4
    4
    4
    4
    4
    4
    4

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Peak # pooled connections
    355
    355
    355
    355
    355
    355
    355

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Total # failed connects
    102
    102
    102
    102
    102
    102
    102

    \\WGSOPMA029\.NET CLR Data(app)\SqlClient: Total # failed commands
    0
    0
    0
    0
    0
    0
    0
    Wednesday, January 02, 2008 5:23 PM

Answers

  • (Is this wrong and should we check for the connection status after query execution)

     

    The command objects you use are connecting through a connection object you need to clean up all your interaction with the database.  I have given you what you need to do I looked but there is no one step fix to your problem.

     

    Friday, January 04, 2008 2:16 PM

All replies

  • You need to go to the security section in SQL Server 2000 under management in Enterprise Manager and add the new network service account in IIS 6 to SQL Server on the server level and database level.  If IIS6 and SQL Server are not in the same server you have to add the network service account on the domain level before the SQL Server configurations.  If you have system admins ask them to configure pass through authentications for you rather complicated for developers but skilled admins can help you.  Check the thread and link below because Asp.net base permissions in IIS6 have changed.


    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2017178&SiteID=1


    http://msdn2.microsoft.com/en-us/library/kwzs111e.aspx

     

    Wednesday, January 02, 2008 7:54 PM
  • Thanks Caddre. One information i had missed out (and apologize for) is that the application is a windows service. Will the procedure you've specified would work for a windows service also?
    Thursday, January 03, 2008 6:15 AM
  • No in Windows service you have to run the system stored procedure sp_who to find out who is connecting to SQL Server without permissions then add that person in SQL Server on both levels and if you Windows service in a different box you need to add that person on the domain level also.  If you have many people using the Windows service and sp_who return all you have to add all either manually or through group policy or pass through authentication which is a better option.  I have found how to fix the problem in details.

     

    http://www.15seconds.com/issue/040830.htm

     

    Thursday, January 03, 2008 3:51 PM
  • Hi Caddre,

    All the connections attempts are with one user name and password only. The ID has complete access to the database. (It is a SQL server user ID). If it is an authorization issue, all the connection attempts should fail, which isn't the case. Under less loaded conditions, it succeeds. But under heavy load conditions, as seen from the performance counters attached earlier, about 1/3 rd of the connections are failing.

    Thanks.
    Thursday, January 03, 2008 5:42 PM
  • That means you are having connection leak issues which means your connection are still open when not used, since the application is in C# you need to add the second Using statement which calls dispose for you automatically on classes the implements IDisposable interface the connection object is one such class. This will take time because you have to clean up every open and closed connection in the application as covered in the last link I posted and here is another from Bill Vaughn he created the original ADO while at Microsoft.

     

    http://msdn2.microsoft.com/en-us/library/aa175863(SQL.80).aspx

     

    Thursday, January 03, 2008 6:35 PM
  • Hi,

     

    are you trying to get the connection from the pool?

     

    i didnt see the "Max Pool Size" and "Min Pool Size" parameters from connection string.

     

    Can you show the entire error?

     

     

    Friday, January 04, 2008 12:53 AM
  • Hi Fabio,

    Data Source=***;Initial Catalog=***;User Id=***;Password=***; This is the connection string. We dont specify the pool size. I was unable to get the message of the exception as it happened in our production environment. Please also have a look at the performance counter logs i had posted earlier.

    Hi Caddre,

    I ran the SP_Who command and was able to see a few connections (very few) opened since '01/01 00:00:00' to the DB server from the app server.

    There is a generic module that we use for interaction with database. All it does is to set the SelectCommand of a DataAdapter and uses a fill command to populate a dataset - dataAdaper.fill(dataset). As per my understanding, we need close a connection in case we dont open it. Is this wrong and should we check for the connection status after query execution?

    Please let me in case extra information is needed.

    *** - populated with appropriate value and read from config file i.e. one user id to connect to the database hence authorization cannot be an issue.

    Friday, January 04, 2008 2:03 PM
  • (Is this wrong and should we check for the connection status after query execution)

     

    The command objects you use are connecting through a connection object you need to clean up all your interaction with the database.  I have given you what you need to do I looked but there is no one step fix to your problem.

     

    Friday, January 04, 2008 2:16 PM
  • Hi Caddre,

    Disposing the connection did the trick. Thanks a ton for your help. It would really be great if you guys publish a Best Practises document in the forum.

    Lesson learned (for me) is that though we may not have opened the connection in using a DataAdapter, we need to Dispose the connection object so that no connections are left unclosed.
    Monday, January 07, 2008 3:23 PM
  • I am glad to see your problem is resolved and yes all interaction with the database starts with a connection object always make sure it is disposed when not used.

     

     

    Monday, January 07, 2008 5:22 PM