SQL Management Studio 2008 R2 "wrongly" reconnects after connection loss

Question

up to a while ago I used SQL2k5 exclusively but recently got updated to 2008 R2. Apart from the obvious changes in Mgmt Studio, there is one quirk that's starting to be very annoying : each time a connection is dropped, I have to switch back to the 'lost' database again as it seems the conneciton automagically reconnected to the initial database again.

• In SQL 2005 I simply had to press F5 twice and the first time it would give me an error saying the connection was lost, the second time it would reconnect to the database it was on before the connection got dropped and then execute whatever sql-commands it had. It did not really matter how I had gotten to that database, be it by using the dropdown-box on top, or a USE statement...

• In SQL 2008 (R2) I now press F5 and mgmt studio will "eat" the lost connection silently and instead immediately reconnect to the server and execute the code on the default database or the database that I 'forced' while connecting using the [>> options] button/tabs

This happens quite a lot as I often have one tab open that kills all connections and restores the database, and another (series of) tab(s) with changed procedures, test-cases etc...

Is there some (hidden) configuration to (re)set this behaviour ??? I know I can try to add USE statements on top everywhere, or 'force' every connection directly to it's 'target' database, but bye bye for ad-hoc queries then =(

ps : doing some extra searching I'm wondering if this isn't due to the "fix" bespoken here [connect.com]

ps: as a side note, after reconnecting the SPID on the bottom of the screen isn't updated properly either, as a result I've already been killing the wrong connection as I based myself on stale information ... yay for progress =( (**)

Anyone with better google-fu than me ? Or closer connections to Microsoft ? =)

Thx.

(**: man, I so miss Query Analyzer =)

Answer 1

If you register the instance you're connecting to in management studio, you can go to "Connection Properties" on the registered instance and set "Connect to database" to be the main database you use on that instance. When you are disconnected and it auto-reconnects, it will use that default database again.

Limitations:

• You can only set this to one database per instance, by design.
• You need to connect using that registered instance to get it to work (right click on it, then 'New Query'). If you just do a new connection without going to the Registered Servers pane, it won't apply the properties to the connection.

Certainly not a perfect solution, but perhaps better than nothing.

PS: Connect bug for incorrect spid is here. It looks like there is a promised fix in Denali

Note: rereading, I see you're already setting the database on the advanced options for your connection at times. This is no more helpful than that, of course, just prevents you from having to do it each time.