Results 1 to 12 of 12

Thread: Intermittent Connection Issue

  1. #1

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    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?
    My usual boring signature: Nothing

  2. #2
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Intermittent Connection Issue

    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.

  3. #3

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Intermittent Connection Issue

    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.
    My usual boring signature: Nothing

  4. #4
    PowerPoster
    Join Date
    Nov 2017
    Posts
    3,116

    Re: Intermittent Connection Issue

    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.

  5. #5
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Intermittent Connection Issue

    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.

  6. #6
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Intermittent Connection Issue

    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.
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  7. #7

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Intermittent Connection Issue

    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.
    My usual boring signature: Nothing

  8. #8
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,393

    Re: Intermittent Connection Issue

    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.

  9. #9
    Banned
    Join Date
    Sep 2022
    Posts
    16

    Re: Intermittent Connection Issue

    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

  10. #10
    Fanatic Member Peter Porter's Avatar
    Join Date
    Jul 2013
    Posts
    532

    Re: Intermittent Connection Issue

    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
    Last edited by Peter Porter; Oct 5th, 2022 at 04:47 AM.

  11. #11
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: Intermittent Connection Issue

    Bibbidi-Bobbidi-Bot .
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  12. #12

    Thread Starter
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Intermittent Connection Issue

    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.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width