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