In Azure SQL DB, what does “The connection is broken and recovery is not possible” mean?

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 connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. All you have to do is hit F5, but I can't be bothered to do that for you.

The connection is broken and recovery is not possible.

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.

Previous Post
Are nulls stored in a nonclustered index?
Next Post
How to Implement In-Memory OLTP Quickly and Easily

10 Comments. Leave new

  • this happens on prem too. Usually after a spid is killed. I’ll have to try this, thanks!

    Reply
  • 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.

    Reply
    • 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.

      Reply
  • Mark Freeman
    March 8, 2019 9:51 am

    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.

    Reply
  • Joseph Gooch
    March 8, 2019 3:40 pm

    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….

    Reply
  • 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.

    Reply
  • Brian Boodman
    March 11, 2019 6:07 am

    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.

    Reply
  • Yes, this annoys me most of the times and have to reconnect to establish a connection. I like the simplified paraphrasing 🙂

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Fill out this field
Fill out this field
Please enter a valid email address.

Menu
{"cart_token":"","hash":"","cart_data":""}