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.)
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.
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 email@example.com.
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.