Freigeben über

Connection Loss Errors in SSMS Query Editor

  • Artikel

Connection Loss Errors in SSMS query editor

Under various situations, connection loss errors in Query editor can cause SQL Server to drop the connection with the Client. Details regarding connection drop errors when working with on-premise server instances and SQL Azure instances are described here and here respectively.

Until recently, the idle connection time-out period in SQL Azure was 5 minutes. This implied if a user opened an Query Editor window in SSMS (connected to SQL Azure) and left it idle for 5 minutes and then went back to Editor to execute a Query, he would hit Idle Connection Timeout error and the execution would fail with the following error –

A transport-level error has occurred when sending the request to the server. (provider: TCP Provider, error: 0 - An established connection was aborted by the software in your host machine.)

Behavior in SSMS SQL Server 2008 R2

In order to have the user not see the effect of frequent timeouts from a remote Azure host, we added logic to catch the Connection Loss exceptions, reconnect and re-execute in SQL Server 2008 R2. Hence the end user would not see any idle connection timeout exceptions while doing Query Execution in SSMS in SQL Server 2008 R2 (and Denali CTP1/CTP2). However, we implemented this fix for all kinds of connection loss errors and for all Server versions (Azure and Single Instance).

This fix introduced a bug related to wrong database context switching during re-connection in some scenarios (see here and here).

While fixing this bug during Denali CTP3, multiple reasons caused us to reconsider our decision to auto-connect disconnected SSMS editor windows.

  • The Idle Connection timeout period had increased to 30 minutes in SQL Azure. Hence the idle connection timeout is not as frequent (as every 5 minutes) anymore.
  • We got feedback around doing reconnection/re-execution under the covers. Some users had the feedback that surfacing connection drops errors is more realistic and informed than doing under the covers re-execution (especially for high severity errors).  If they lose connection due to any reason, they should see the error in the Results/Message pane, and they can click Execute again to try re-execution.  Also, they are used to that behavior for on-premise servers, and it existed before 2008 R2.

Change in SQL Server Code Named “Denali” CTP3

Considering all the feedback mentioned above, we have removed the re-connection/re-execution logic altogether from the SSMS Editor in SQL Server Code Named “Denali” CTP3. This means SSMS users will see the idle connection timeout exception if they try to execute a query in an editor window whose connection has remained idle for more than 30 minutes. The Editor status bar would also show “Disconnected”. Afterwards, user can hit “Execute” again to re-execute and re-connect. The behavior will be similar for all other connection loss errors in SQL Azure. This behavior is same as what existed before 2008 R2 for On-premise Database.

The ask for re-connecting to the correct database context, before the time-out occurred, is still valid, and will be considered in a future SQL Server Release.

Update: We have removed the re-connection/re-execution logic for SSMS editor in SQL Server 2008 R2 in the Service Pack 1 release.

---

The author Nitika Nathani, is a developer in the SQL Server Manageability Group. She can be reached at nnathani@microsoft.com.

Comments

  • Anonymous
    April 16, 2012
    Is there any configuration to increase timeout time from 5 or 30 minutes to say an hour? The links do not provide any solution from what I read. Thanks

  • Anonymous
    May 31, 2013
    The comment has been removed

  • Anonymous
    April 21, 2014
    It is so nice article. I was really satisfied by seeing this article and we are also giving Tibco Online Training. Tibco <a href="http://www.tibco-online-training.com">Tibco online training</a>is best online training institute in USA.

  • Anonymous
    July 30, 2014
    thanks

  • Anonymous
    October 16, 2015
    Why you are not blogging anymore? Can you please write some articles on SQL SERVER 2014?