SSMS hangs for an extended period when closing a query tab or object explorer when a connection cannot be established to the SQL Server

allmhuran 136

Suppose I am connected via VPN to a network that includes a SQL server.
I then open SSMS and connect to that server.
Eventually the VPN loses connection or I disconnected (work is finished for the day, whatever)
I then attempt to close an SSMS query window, or close the SSMS application.

At this point, SSMS will hang, becoming completely unresponsive. It seems as though it is trying to create a connection to the now unreachable server. I assume that this attempted connection eventually times out, because after a while SSMS starts responding again and the query window closes. At no point is any kind of error shown.

I don't see any reason why SSMS should be attempting to make a connection to the SQL Server when I am just closing a query window. If I was attempting to execute a query that might make sense, but not to just close a tab, or close SSMS as a whole.

This is currently happening on SSMS 18.6 but has been happening for multiple versions now. It has become a particular nuisance with significantly more work being done remotely via VPN.

Randy S 36 Reputation points

2022-04-19T12:42:58.71+00:00

I agree, something needs to be done with this. I support several clients with different VPN's. I often don't close my windows. Then later if I want to close them or SSMS entirely, I have to wait for EACH one to check the transaction count and time out. I have mine set to auto commit, so there won't be any open transactions anyway. This has only bee in the last few releases from SSMS. It most definitely did not used to do this. Right now, I need to shut down SSMS to reboot, and I'm going to have to just do an end task on it, or I'll be here all day waiting for these "transaction count" checks.
Erland Sommarskog 101K Reputation points MVP

2022-04-19T21:28:02.227+00:00

This has only bee in the last few releases from SSMS.

Yes and no. I did not check each and every build, but SSMS 2008 and SSMS2014 both behave this way. Well, they don't have the dialog - that one is new for SSMS 18.11. But the trancount has always been there. It is also in Query Analyzer for SQL 2000. However, the older versions seems act more gracefully when the connection to SQL Server has been lost and do not hang.

By the way, the new Azure Data Studio does not seem to make this check, so may you would like try it out.
Nathan Bell 26 Reputation points

2022-04-19T23:37:41.977+00:00

I too have now resorted to killing SSMS through task manager, which is impractical to say the least. I don't understand the logic here; I mean, if a connection has been broken, you've lost any open transactions anyway, so why even try to reconnect? Also, the 'useful' pop-up is still there (try closing a tab with an uncommitted transaction), so I'm not sure what purpose this new behavior is supposed to serve.
allmhuran 136 Reputation points

2022-04-20T00:32:52.553+00:00

I had the same thought, and Erland and I discussed this in a different subthread, in which I wrote a little test program using .net. The short story is that at the level of user code (in SSMS) the system is unaware that the connection is broken, and then the thing that actually attempts to reconnect is not the SSMS code, it's the driver itself. This reconnection attempt cannot be intercepted at the level of the SqlClient, nor can the timeout be changed dynamically (since it's set by the connection string and is not accessible after that).
Dan Guzman 9,206 Reputation points

2022-04-20T00:59:25.553+00:00

@allmhuran , I think you are referring to the behavior of idle connection resiliency, where the driver attempts to reconnect transparently when a command is executed on a supposedly good connection. That's at a low level not controlled by the client app like you said.

However, it kicks in only when a command is executed on the connection. I think the suggestion here is for SSMS to have an option to avoid executing any commands (e.g. SELECT @@TRANCOUNT) on the connection when a query window is closed. That way, the window could close immediately without the driver et. al. getting in the way.
allmhuran 136 Reputation points

2022-04-20T03:10:59.157+00:00

Thanks muchly for providing that link, I was not aware of the term "idle connection resiliency" or the details of the implementation, so I was only deducing from what I observed. But yes, that's exactly it.

The actual suggestion that was created is to run that connection "in the background (or async)", but I don't think that can be done given that it's not the client code re-opening the connection. I did propose that the entire operation might be done in the background with a short cancellation token, but I don't know if the cancellation would be honoured, I didn't try that in my test. Eliminating the trancount check entirely is of course an option though. Erland and I discussed a couple of other possibilities down-thread.

In any case it sounds like there's some kind of plan because the suggestion response indicates the behaviour might be changed in the next version, so that's great news.
Cory Larson 1 Reputation point

2022-06-02T14:42:45.887+00:00

I experience this same issue -- I will have SSMS open with some tabs connected, then I will lose VPN connection. At the end of the day, trying to close those tabs, even if those files are already saved, takes almost exactly 30 seconds, every time. I recently took to Procmon to see if I could spot any strange activity, and what I found is 32 attempts coming from SSMS to reach \servername\PIPE\sql\query, which end in "BAD NETWORK PATH". They occur about once per second:

The events captured look like:
10:30:50.8413544 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:50.9819516 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:51.2176296 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:51.6437132 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:52.4757236 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:53.5039366 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:54.5464529 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:55.5677987 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:56.6097402 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:57.6419288 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:58.6652511 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH
10:30:59.7016847 PM Ssms.exe 21996 CreateFile \redacted\PIPE\sql\query BAD NETWORK PATH

And so on.

Accepted answer

Erland Sommarskog 101K Reputation points MVP

2023-01-26T19:57:49.4066667+00:00

SSMS 19 was released today. And there is no an option to turn off this question, see below. Note that the checkbox is checked by default. I unchecked first thing when I got SSMS 19 installed.
Please sign in to rate this answer.

6 people found this answer helpful.
Bev O 0 Reputation points

2023-08-18T14:22:12.86+00:00

I cannot upgrade to 19 right now, so this isnt helpful. This has only started happening recently on my VM, nowhere else, so I want to switch it back off

Erland Sommarskog 101K Reputation points MVP

2023-08-18T21:13:10.9+00:00

First, you don't really upgrade to SSMS 19. It's a side-by-side installation.

While you may only have seen this recently, there is nothing you can turn off. SSMS 18 always behaves in this way, and so did all other versions before SSMS 18. (But SSMS 18.(some version) was the first to introduce the creepy dialog.
Sign in to comment

8 additional answers

Erland Sommarskog 101K Reputation points MVP

2022-03-15T22:41:16.53+00:00

When you close the window, SSMS issues "SELECT @@trancount" to see if you have any open transaction, so it can alert you if you have. Keep in mind that SSMS is not aware of that the connection has gone dead, so it will try to run the query (and eventually give up).

Yes, it happens to me many times. I always make the mistake when it is time to close down everything before I restart to complete a Windows update. I first close my VMs and I close the query window connected to the VM. Or I keep a window open to my serverless Azure SQL database, and when I close the window, the database has been shut down.

If you would like to have an option to skip this check, you could file a suggestion on https://feedback.azure.com/d365community/forum/04fe6ee0-3b25-ec11-b6e6-000d3a4f0da0. Be sure to include why you want that change and the business impact, because this is important for Microsoft.
Please sign in to rate this answer.

5 people found this answer helpful.
allmhuran 136 Reputation points

2022-03-16T10:33:38.487+00:00

Thanks Erland, I did not think of the query for trancount. It's been a while since I've seen the "there are active open transactions, are you sure you want to close the tab?" dialog!

So, my thought that the application doesn't seem to have any need to query the server on tab-close was wrong, at least given the current implementation. I might raise a feedback ticket to at least have that check done via async so other tabs (possibly to other servers that are still connected) can still be used while it thinks about that... or just check the connection state first I suppose.

Nathan Bell 26 Reputation points

2022-04-14T04:38:04.867+00:00

A suggestion has been opened for this. You can find it here:
https://feedback.azure.com/d365community/idea/aaaad978-65b9-ec11-a81c-6045bd80aaa9

Dan Guzman 9,206 Reputation points

2022-04-19T23:48:11.66+00:00

Thanks for taking the time, @Nathan Bell . Upvoted. I've been meaning to submit that suggestion myself for some time.

Nathan Bell 26 Reputation points

2022-04-20T02:16:24.66+00:00

Credit for opening it goes to Irinel Lupei; I just wanted to give it visibility. I notice there is now a comment on that feedback issue: "We have logged an issue internally and are planning to address this in the next release." :-).

Luke poga 0 Reputation points

2023-10-22T20:59:28.5233333+00:00

This is still broken in SSMS 18 as of October 2023! It will probably never be fixed. I guess solution is to install SSMS 19.
Sign in to comment
Dan Espich 41 Reputation points

2022-08-03T19:56:24.017+00:00

Yea I am confused on this. If it's something that has always been done when you close a window/connection why does it suddenly make you click "close" on the notification? It's a extra unneeded click, I don't really care to know that "Querying transaction count" is complete, just close the window or give me the prompt to save changes or not.
Please sign in to rate this answer.

3 people found this answer helpful.
BP 6 Reputation points

2022-08-04T10:20:53.323+00:00

I totally agree, that is why I wrote that "So, the way I see it is this being a disclaimer or a debug window to show what is happening instead of the SSMS hanging the UI."

RichardB 6 Reputation points

2022-08-23T20:46:07.323+00:00

(Also see my reply to BrunoPimenta's answer.) I'm no longer getting the same behavior even though SSMS version has not changed. I think this topic is something of an "echo chamber" since the only people seeking it out are those that are experiencing the issue.

Yesterday, I was getting the pop-up every single time I closed any query with an open connection.

When I was testing just a few minutes ago, the functionality seems to now be working as desired. The pop-up message is only presented if the transaction check is taking a long while due to the connection being severed, and I can click "Stop" on the dialog instead of waiting the full 30 seconds. This is desirable compared to all of SSMS just hanging while it waits.

Sidenote: It's a little strange to me that it will check for open transactions on closing the query and also when changing the connection, but not when you explicitly choose "close connection" (in which case, I assume if there are any open transactions they will just rollback).

Erland Sommarskog 101K Reputation points MVP

2022-08-23T20:56:38.95+00:00

When I was testing just a few minutes ago, the functionality seems to now be working as desired. The pop-up message is only presented if the transaction check is taking a long while due to the connection being severed, and I can click "Stop" on the dialog instead of waiting the full 30 seconds. This is desirable compared to all of SSMS just hanging while it waits.

This behaviour is what I have seen since 18.12 came out. In 18.11 that Stop button had no effect.

But it is still kind of irritating. The other day I wanted to shut down my computer. Since I had just started it, I had nothing in SSMS, so I did bother to close SSMS first. But the shutdown was still delayed because of the transaction check. The window was connected to a local instance, and which apparently stopped before SSMS.
Sign in to comment
Naomi 7,361 Reputation points

2022-03-15T19:20:16.957+00:00

Hi,

I can only confirm that I also observed this behavior (even in SSMS 18.10). If I left a query window opened and accidentally try to execute it after not having a connection to the server, closing the file or cancelling the query takes a long time. And just saving the file (without even executing) may also take a very long time.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor

2022-03-16T02:47:50.243+00:00

Hi allmhuran-6523,
Please also try to use SSMS 18.11.1 to see if the issue have been solved.
If the issue persists, suggest you submit the issue to Microsoft feedback website as Erland mentioned.
Your feedback is valuable for Microsoft to improve the level of products and service. Appreciate for your understanding.
Best Regards,
Amelia

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Please sign in to rate this answer.
allmhuran 136 Reputation points

2022-03-16T11:07:40.71+00:00

There is a change of behaviour in 18.11.1 in that it now pops up a "querying transaction count" dialog when the tab is closed. But this doesn't really improve the user experience. Yes, it does say why it has become unresponsive, but the popup dialog is modal, so SSMS is soft-locked while that is happening. Additionally, if I click "stop" on that operation it doesn't stop, it still waits for the timeout, so the duration of the soft-hang is just as long. Finally, when it times out it pops up a message that says "unable to determine if there are transactions open, do you want to continue and close the window?". If I hit no, it still closes the window. Rather unhelpful overall and not a change that seems to have been though through very well.

Erland Sommarskog 101K Reputation points MVP

2022-03-16T22:09:50.23+00:00

Interesting! I have not noticed this one, but 18.11 has not been out very long.

As you say, this is so-so. Not only because that the dialog is modal. But consider the situation where someone has pulled the network cable, and you run a query. SSMS will now be locked until the connection attempt times out. This occurred to me several times to day, because I was occasionally running queries against an Azure SQL Serverless Database, which got unloaded between my queries.

I have also experienced hangs when in Object Explorer for reasons I have never been able to identify.

And, yeah, it is a little crappy. Something like SSMS should never hang just because a single query window has problems.

KRemmes-2662 6 Reputation points

2022-04-03T22:16:16.83+00:00

I totally agree that this "feature" is very irritating. Working over VPNs all day long accentuates this disconnect "hang" issue on the "querying transaction count" dialog. Please give us an option to turn this check off. I had to go to 18.11.1 to fix another issue with Azure and sql agent jobs so unhappy to fix one issue to discover another issue.

Erland Sommarskog 101K Reputation points MVP

2022-04-05T18:55:10.023+00:00

The only change in 18.11 is the explicit dialog. The trancount check has always been there, and also in the past, SSMS would hang if you already had lost the connection to the instance.

I see little point with an option - we cannot have options for everything - but it would be better if the Stop button actually worked...

allmhuran 136 Reputation points

2022-04-06T10:11:54.37+00:00

Thinking about this a bit more - and of course without going through the SSMS code - it seems like the transaction count check shouldn't even be done. After all, if the connection is broken there can't be an active transaction. So the best solution here, as far as I can see, would be to check the the state of the connection explicitly as the first step, and only run the tran count check if the connection is open. If this was done, then there'd be no soft-lock on closing a window with a broken connection.

Having said that, I'm just assuming that SSMS is using ado.net here, and I'm also assuming that if the underlying network connection is broken, then the SqlConnection's state would be ConnectionState.Closed, and either or both of those assumptions could be wrong, so I suppose there may be some complexity here that would make my proposed solution not quite as trivial as it appears.

Erland Sommarskog 101K Reputation points MVP

2022-04-06T21:05:19.5+00:00

Having said that, I'm just assuming that SSMS is using ado.net here, and I'm also assuming that if the underlying network connection is broken, then the SqlConnection's state would be ConnectionState.Closed,

If the connection state is indeed Closed, it is of course nutty to reconnect to check if there is an open transaction!

However, since what has happened is that the network cable has been pull, I would not expect that the connection object is aware of that the connection has gone dead.

If you like to play with it, you could write a simple console application that opens a connection, runs a query, stays connected, waits for user input. And while it's waiting for user input, you close down the VPN connection. Then you press return and the application now tries to run a second query on the connection it believes to be open. What happens?

allmhuran 136 Reputation points

2022-04-07T02:45:53.16+00:00

I wrote a little test console app and wrote out an answer with details, but it either got eaten by the forum or there's a delay for moderation approval.

In any case, the short story is that the connection does not switch state to closed when the VPN is disconnected, and the next query attempt honours the timeout set in the connection string on the SqlConnection object.

The interesting thing about that... if the connection timeout is being honoured, then something "knows" that a new connection is being attempted, which should be sufficient to know that there can't be any open transaction. But there's no explicit "open connection" code being executed for the attempt, I only issue an await SqlConnection.OpenAsync once prior to starting the loop. So I guess this reconnection is being made at the driver level, and so I don't know of any way of short-circuiting this driver-induced connection attempt by, for example, supplying a cancellation token from a cancellation token source with a very short CancelAfter duration, since it's not happening in user level code.
Sign in to comment