Intermittent Connection Issue

I have a SQL Server Express 2017 instance running on a computer on my home network. I am developing/testing a different program on a different computer that connects to a DB on that SQL Server instance. Normally, this works fine, but occasionally it does not.

I asked about this several years ago, and somebody (I think it was Techgnome) gave me a very useful suggestion. If the computer with the DB has gone to sleep, occasionally it doesn't wake up right away. So, if there is a failure to connect at first, I wait 3 seconds and try again. Since this works most of the time, I haven't spent any great length of time examining the few times when it doesn't, but I just noticed something odd.

I ran the program, it connected to the DB without any issue...and then I found a bug, so I stopped the program and fixed that quickly. I was referencing an out of date version of a DLL, so it was just a matter of swapping in the correct DLL, which was a very quick fix. The computer hosting the DB certainly had no opportunity to go back to sleep in the seconds it took me to make that fix, but when I started it up again, the program failed to connect to the DB.

When connection fails, a few things get tried, which takes a couple seconds, then a window comes up that shows the various SQL Server installations, and I can test the connection against any of them. I chose the right one, and ran the test, and it connected without issue.

So, the program connected right away, the first time, but when I ran it a few seconds later, it failed...waited three seconds, then failed again, and after a bit of waiting...all was well.

I'm kind of at a loss as to what could be going on here. Being so intermittent, it's hard to do much diagnosing, since I can't count on the issue happening in any predictable fashion. So, what I'm looking for is any suggestion as to why a connection over a WiFi network can connect, then fail to connect the next time, which is maybe a minute after the successful connection?

Check the Event logs on the device running SQL Server to see if there was any indication of either the SQL services crashing and needing to restart or any indication of a loss of network connectivity during the time frame when you couldn't connect.

I don't believe there was a crash, but network connectivity is always a possibility when it comes to WiFi, especially in a noisy neighborhood. Not sure that SQL Server would have noticed that, though.

Beliefs are a troubleshooting last resort.

Check the event logs on the server. You may find out things you had no idea were going on.

If there was a loss of network connectivity, I wouldn't expect an idle SQL server instance to know or even care, but it should be logged in the System event log when the connection was lost and when it was restored. At least, that's what happens with wired connections. With wireless connections, I can't speak with certainty, since I exclusively use wired on my devices.

It is also possible that the power profile settings for the network adapter(s) are on a setting like "power saving" or something similar. I always go into the power settings and make sure that everything is set to "always on" or "maximum performance" so that things do not get turned off.

Why do I remember you having a problem with your rooter and said to try another but never did?
Of course I might be wrong as this was 1-2 years ago.
Thing is, you might want to try a different rooter to see if the problem persist. Of maybe a firmware upgrade.
Since you said you swapped the dll, maybe that new dll not work good with an older rooter. Anyhow that's more or less what I can't think about. Other than that, what Option and jdc wrote. Both SQL and Server logs.

That was a while back, and since that time, the router I was using completely died and has been replaced. Still, you're right about that.

I'll have to check the logs, when I get a chance. There could be something intermittent, especially on a wireless network. It used to be that I'd see three or four other networks available (some weren't even locked down), but now I see well over a dozen. That's noisy. However, this wasn't a case of a connection dropping while I was connected. It was a case of a connection not being established a few seconds after it had been. I've never had one drop while in use, so if this was a case of the connection dropping, that would be quite the coincidence.

On the other hand, the problem has steadfastly refused to manifest a second time, and here I am all set to check a few of my assumptions if it would be so good as to happen a second time. If it's a one-off...well, that's both good and bad. If it's somewhat rare, I'll be able to get some data if it would only happen again. If it's REALLY rare, then...well, I've already come up with a way to (possibly) solve it that won't require much of a change.

Power saving issues is an interesting one. That certainly DID have an impact in the past, and might be the source of this issue. That's why I added the three second delay, but that figure was picked at random. Magic numbers are not always magic. I really don't want to turn off the power saving features that are enabled, but I need more data before doing that would be worth a try anyways. After all, one event doesn't mean a darn thing.

You may also have an issue with very brief power drops on your networking equipment. I have all of my routers and switches, and computers on UPS systems. Otherwise, I would have multiple dropouts daily.

Once you’ve identified intermittent network problems or intermittent connectivity issues, plaguing your network, a great monitoring solution will also allow you to collect the data you need to troubleshoot these network problems and optimize network performance.

When troubleshooting network slowness or network problems, several tools are available in the vault of a network administrator such as:

Traceroutes: to identify route, latency and packet loss between two sites and detect exactly where and when network problems happened. See visual traceroute results and troubleshoot with traceroutes using Obkio's Vision Visual Traceroute tool.

Speed Tests: which allow you to validate if the bandwidth you need is readily available.

Prioritization and QoS: which increases network efficiency in the event of network congestion and prioritizes applications more critical or sensitive to performance problems such as VoIP or video.

Network Device Monitoring: to get detailed information about the health of network devices such as firewalls, routers, switches and wifi access points

mmarkgilbert is definitely a bot. Seeing now, alot of it's replies yesterday were posted within 1 to 3 minutes of each other.
https://www.vbforums.com/search.php?...st&showposts=1

A big dead giveaway was it's "how to's" reply that provided no instructions:
https://www.vbforums.com/showthread....t=#post5582024

Bibbidi-Bobbidi-Bot .

That issue will be resolved in two days, one way or the other.

I got a bit more data the other day...too bad I forgot what it was.

Actually, what happened was that I put a couple breakpoints into some code that would only be reached in the event of a connection issue. One of the breakpoints was reached. This suggested that the issue was a timeout for reasons not quite understood. Unfortunately, I then had the opportunity to screw up and promptly took it. There are two DBs offered up, an old one that has lots of out of date stuff, and the correct one. I should have turned off the bad one years ago, but didn't for reasons I can't explain. In any case, I screwed up and chose the wrong one....then spent the next several minutes both fixing that mistake (the program remembered my choice...to my detriment), and taking that other DB offline.

So, the only thing I really gained out of that was that I learned that one of my assumptions was correct, and I changed up the way the search works so that the problem 'might' simply go away. At least I made a change that will let me know if the change did fix the problem.