>>>>Hi,
>>>>
>>>>I have a code suggested by Walter Meester that tries to reconnect to the SQL Server, if the connection (for whatever reason) fails. The code uses a DO WHILE loop trying to reconnect. Currently I have it set to 120 seconds. But this seems to be excessive, since for a user, waiting for 120 seconds could seem like ages.
>>>>
>>>>What is the reasonable time trying to re-connect, before giving up?
>>>
>>>Starting with the early days of mainframes, a "reasonable" response time to user interaction was considered to be 2 seconds.
>>>
>>>This century we've been spoilt by Moore's Law and its resultant fast computers and networking. Many users get impatient if UI response time is more than half a second. That said, if an app is specialized and known to involve intense computing, the old 2-seconds rule of thumb is still reasonable, and even longer may be acceptable or considered "normal".
>>>
>>>To narrowly answer your question, you could set a timeout which is some multiple of the normal response time, maybe 5x or 10x. If that multiple ends up to be less than 2 seconds, you could set it to 2 seconds. I assume with longer attempts you're already giving the user some sort of "Retrying - please wait..." feedback.
>>>
>>>The larger question is, why do you have to do this in the first place? I don't have a client-server app which I have to support across multiple customer environments, but I have worked against several types of RDBMSs over LANs, and SQL Server in particular over a WAN connection. Once established the connections have been quite reliable. Where I've included retry code I don't recall ever seeing any logs where it had been called except for major problems such as network or server outage.
>>>
>>>I seem to recall you've posted several threads this year about loss of connectivity to SQL Server. Do you need to retry because you're seeing disconnects with multiple customers, or is it one, or just a few, out of many? If it's the latter, maybe something in the customer environment is to blame e.g.
>>>
>>>- Limiting concurrent connections to SQL Server, and that limit is being reached: https://learn.microsoft.com/en-us/sql/database-engine/configure-windows/configure-the-user-connections-server-configuration-option?view=sql-server-ver16 . A DBA might do this for licensing compliance
>>>- Some sort of strict RDS management (session resources, connections etc) if RDS is in use
>>>- General networking issues - misconfiguration, DNS etc.
>>
>>Thank you for a detailed reply to my message. I do have a couple of customers who, once in a while get the error connecting to the SQL Server. I periodically look at the log and see this. Plus, when it happens to them, I get an error email message. At least one customer uses many Windows tablets with my app. They work 24/7. So, I presume that when they put a tablet down for some time, the tablet loses connection to the SQL Server.
>>Walter Meester wrote in one of his messages that he has the same issue. Because his customers are all over Europe, he - when carrying his computer somewhere - loses connection. He wrote of an approach that works for him. I use his approach. I have never seen a customer having to wait for the connection. Although I do see in the log that they would lose connection and then the application quickly reconnects (thanks to Walter's code).
>>My code that reconnects has the DO WHILE loop of 120 seconds. So far I have never seen this loop being executed to the end. Usually a second or two is enough. But I do want to continue using this DO WHILE loop. I just need to reduce the number of seconds from 120 to something more reasonable. Just in case.
>>I read your message that 2 seconds is an acceptable value. Just to be "conservative" I will change the 120 seconds to 10. At best, the application will never reach this time (10 seconds). But at worst, it may happen.
>>I have a log that monitors it.
>>Thank you!
>
>OK, if you have mobile devices/Wi-Fi in the mix you might see disconnects. Someone nearby microwaving a burrito and killing the Wi-Fi, or as you mentioned, a mobile device going to sleep and saving power by dropping the network connection. I was lucky in my clients not needing those, or me being in a position to dictate wired Ethernet.
>
>Microwaving a burrito might take more than 10 seconds :(

I don't think I ever had a burrito, so who am I to judge :)