none
Connection closed even though it is not idle

    Question

  • Hi,

     

     I have deployed my application on Windows Azure and it uses a Sql Azure database. My application has it's own connection pool that maintains connections needed by the application. The pool maintains certain number of connections (configurable) and gives them out to the application, whenever requried. The pool validates every connection before giving it out by executing a 'validation sql'.

    I intermitently experience an exception. - A transport-level error has occurred when receiving results from the server. (provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.) 

    I know that Sql Azure closes idle connections after 30 minutes but what I do not understand is that since every connection is validated by the pool before being used by the application, it definitely is not idle for 30 minutes. The connection is being closed after the validation and before it's being used by the application, which is almost immediately. 

    The query run on the validated connection is also very simple and yields a small result set. So it can't qualify as a long running query/transaction.

    Does Sql Azure close connections that have been open for a certain time i.e after a certain 'age'. I can't think of a better explanation.

     

    Any ideas/hints?

     

    Wednesday, September 15, 2010 9:14 AM

Answers

  • Connections can be closed for several reasons. The recommended best practice I've seen when dealing with SQL Azure is to not leave them open. Open it right before you need it and close immediately after the operation is completed. This not only helps reduce occurances of this, but also helps you maximize the number of processes that can then access your limited supply of SQL Azure connections.

    You can find a list of considerations at: http://msdn.microsoft.com/en-us/library/ee730903.aspx

    Look at the "Developing SQL Azure Applications" section.

    • Marked as answer by Yi-Lun Luo Thursday, September 23, 2010 9:38 AM
    Wednesday, September 15, 2010 10:42 AM
    Moderator

All replies

  • Connections can be closed for several reasons. The recommended best practice I've seen when dealing with SQL Azure is to not leave them open. Open it right before you need it and close immediately after the operation is completed. This not only helps reduce occurances of this, but also helps you maximize the number of processes that can then access your limited supply of SQL Azure connections.

    You can find a list of considerations at: http://msdn.microsoft.com/en-us/library/ee730903.aspx

    Look at the "Developing SQL Azure Applications" section.

    • Marked as answer by Yi-Lun Luo Thursday, September 23, 2010 9:38 AM
    Wednesday, September 15, 2010 10:42 AM
    Moderator
  • I wonder what your validation query is. Try using a different validation query; make sure that you SQL requires a real database connection. I wonder if you are not getting a shallow/cached response that is returned to you by one of the intermediate proxies.
    Herve Roggero - SQL Server Firewall and Auditing Consultant
    Thursday, September 16, 2010 7:30 PM
  •  

    Your connection can be closed for several reasons. One is you have too many open, or some of your query is long running, taking up too many CPU time. One thought is your connection is being throttled. You should open only if and when you need it, and close it when you are done. On top of that, to get reliable connection, you need to implement retry.

    This article desribes a bit about connection closing and retry that may help you. IMHO, you must implement retry policy if you want your application to work, period.

    http://blogs.msdn.com/b/bartr/archive/2010/06/18/sql-azure-connection-retry.aspx?utm_source=feedburner&utm_medium=twitter&utm_campaign=Feed%3A+SiteHome+%28Microsoft+|+Blog+|+MSDN%29

     

     

     

     


    Thanks, Scott Ma Schlumberger IT & Software Community Leader. MS SME for Entity Framework and Windows Azure Platform.
    Tuesday, September 28, 2010 6:12 PM