All it really means is, “Click Execute again, and you will be fine.”
If I had a dollar for every time I saw this error message in SQL Server Management Studio:
The error says:
The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection. (Microsoft SQL Server)
That’s not what it means though. Here’s what it really means:
Click Execute again, and things will be fine.
It’s not that your network is broken, or that your IP address changed, or that there’s a firewall error. Something just happened behind the scenes where Azure decided your connection was idle and it needed to do something on the server side.
I wish Management Studio wouldn’t give in quite so easily. Just run your query again, and you’ll be fine.
11 Comments. Leave new
this happens on prem too. Usually after a spid is killed. I’ll have to try this, thanks!
I appreciate the message myself. Loads of times getting that message from a seemingly innocuous query tab has indicated a network problem as being the root cause of an issue I was investigating elsewhere.
For problem, read “hiccup” — thinking about overnight queries that were running from a non-SSMS client with no logging because they were ad-hoc, and the next morning you come in to find that they’ve mysteriously failed, and the pieces fall into place when SSMS tabs open since yesterday start throwing that error. Obviously the lesson to be learned is exception handling, but…the real world.
When I have seen this in SSMS conected to an Azure SQL Database, I often find that clicking Execute again does not resolve the issue. In fact, right-clicking and selecting Connection | Change Connection and then selecting the same database server won’t even work. I have to open a new window and connect to that database and then copy/paste from the old window to the new one, and then finally execute. It’s like the window itself gets in a weird state and is no longer capable of connecting.
Maybe Microsoft should implement their own advice….
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-connectivity-issues
At the very least they could add “Retry?” and perhaps a “Do this every time” checkbox….
HA! Yeah, exactly.
Many times, I’ve discussed with the development team members that there should be no window that ever has both “Connect” and “Disconnect” grayed out, yet I see it all the time.
Yes, and it drives me bananas because I’m sure MS’s own teams are experiencing this issue, too.
Every time you add an auto-retry, you double the time it takes for a failure to be reported. There are ways to cheat (e.g., don’t automatic retry if it’s been more than X time since the first attempt), but it’s not as simple as it first sounds. Further, engineers writing tools for other engineers usually prefer that the tools do exactly as they were asked, rather than doing anything extra to “be helpful.” It’s surprising how often trying to be helpful makes things worse.
I’m not saying this feature is a bad idea. I’m merely warning that it’s not necessarily the obvious win at looks like. Given how easy it is to just try the connection again, this sort of feature is probably not going to receive much priority.
While most of Raymond Chen’s blog focuses on C++, his article on warning of the danger of automatic retries ( https://blogs.msdn.microsoft.com/oldnewthing/20051107-20/?p=33433 ) is very accessible.
Yes, this annoys me most of the times and have to reconnect to establish a connection. I like the simplified paraphrasing 🙂
Except that you cant click Execute again if you had clicked Parse in the first place. SSMS gets stuck thinking its executing the parse and the query buttons are disabled