SQL Server Performance

Sleeping Connections/Processes

Discussion in 'SQL Server 2008 Performance Tuning for DBAs' started by snaveen_333, May 22, 2009.

  1. snaveen_333 New Member

    Hi,
    we have a C# web application with SQL Server 2005 as backend database. we have not disabled connection pooling in the connection string, so by default the connection pooling should be working. we are properly opening and closing the connections. But we have noticed large number of sleeping processes(Awaiting Command) with the oldest connection sleeping is some 5 months back. Is there any configuration setting in SQL 2005 for auto-close of sleeping processes or disallow the process in sleeping state for long duration? We are getting the following for connections to the server now :
    SQL connection get returned error. Message:An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: TCP Provider, error: 0 - Only one usage of each socket address (protocol/network address/port) is normally permitted.) SQLState :[0] ErrorCode : [10048]
    Is this due to the sleeping connections?
    snaveen_333, May 22, 2009
    #2

  2. Elisabeth Redei New Member

    Elisabeth Redei, May 25, 2009
    #3

  3. Read23 New Member

    hello,
    i think so elzibeth tell you the right path and the pasted link described the problem very well and solution
    Read23, Jun 20, 2009
    #4

  4. satya Moderator

    Naveen, Welcome to the forums.
    I believe it is the recordset which has these connections leaving at the server, I would guess that the application is performing multiple connection to the database that you configure to use one connection multiple times.
    By default SQL server will not terminate the connection unless it is initated from the application or you KILL the processes. Such as causing these completed processed connections rather we can class them as orphaned, but still using the memory causing the default error.
    I would also refer to look for any blocking/locking at the time when you see this error as it may be because of a locking query, which is holding one table and others are waiting. A normal select/update/delete/Insert statement is escalated to a locking one if it is taking more time. A select be default is a locking one. The trick is to avoid such long running queries. You should focus on that. OR In your program itself you can set the time out value (query time out).
    satya, Jun 20, 2009
    #5

  5. RickNZ New Member

    How are you closing your connections? Do you have all SqlConnection objects wrapped in using statements or try/finally blocks? Are you calling Dispose() or just Close()?
    RickNZ, Jul 29, 2009
    #6

Share This Page