Weird datasource connection problem

Archived Topics
1

Today for no apparent reason one of our data source connections to one of our SQL servers stop working in the middle of the day. We received a call that some of the clients were receiving errors. When I checked the status of the connection it was yellow and said “this data source is being tested”. I tried making another connection to the same database and it would not connect with the same problem. There is another connection to the same server, but to a different database and the data source was green. I talked with the DBAs to see if they did anything and nothing had changed. After about 30 minutes of working with different connection parameters and checking processes ids in the SQL server I restarted the gateway service and everything worked fine again.

My question is what went wrong? Where could I look to see what caused the issue?

2

I would start by looking for open connections that for some reason are not getting closed. The DBAs should be able to see how many open connections are used in the pool. In my experience this is the first thing to look for. Commonly programmers have a hard time always closing connections especially within error handling routines. For example an error happens while working with the connection and then it is not closed. Once all of the connections are used up in the pool the connection stops working.