Automatic reconnect MS Sever SQL connection

[Current_93]

Dear colleagues,

Please help me about how can I do the automatic reconnect SQL server connection after lost.

Now the "DB open connection" I did at once before "while loop" and I don't want put into "while loop" in order to leave growth memory consumption

[MarcoMauri]

Opening the connection in the while loop should not increase memory consumption, provided you close  each connection after use.

In your case you could close and reopen the connection in the while loop only if an error has been detected.

I usually avoid keeping a connection open it not strictly needed: I prefer to open-use-close the connection each time.

Regards,

Marco

[Current_93]

Dear Marco

With your "open-use-close connection" how much long application works?

Simply my application is working in 24/7  ()  and I try to minimize memory consumption...   Unfortunately LV can't working in uninterrupt mode

[Mark_Yedinak]

THe overhead for the open/close is very little. We have not observed any issues when using this method.

[MarcoMauri]

Opening/closing the connection to a database placed in our local network takes e few ms.

Since we are executing queries about once every 30s we chose the open-query-close approach (which has a "bult-in retry" in a certain sense...)

A look here could help confusing the ideas about what's better 🙂 :

http://stackoverflow.com/questions/312702/is-it-safe-to-keep-database-connections-open-for-long-time

Regards,

Marco

[ArnauDomingoGonzalez · Accepted solution]

We were closing and opening a connection every time and we decided to change because the virtual memory increases considerably. So,  we opened the connection before the while inserting a case inside the while which detects an error with the communication and if thats the case reconnect the communication.

[Steffen01]

how about a SQL state machine, see attached