How to find unclosed connection? Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Question

I've had this problem before and found that basically I've got a connection that I'm not closing quickly enough (leaving connections open and waiting for garbage collection isn't really a best practice).

Now I'm getting it again but I can't seem to find where I'm leaving my connections open. By the time is see the error the database has cleared out the old connections so I can't see all the locked up connections last command (very helpful last time I had this issue).

Any idea how I could instrument my code or database to track what's going on so I can find my offending piece of code?

Answer 1

The error you are providing doesnt really point to a connection that is left open; it is more likely that there is a query that is taking longer than the application expects. you can increase the time it waits for a response, and you could use Sql to find which queries are the most taxing.

Answer 2

Hopefully you have one data access layer class, instead of a whole bunch of classes, each one creating its own connection, right? What language are you using? If your using C#, the biggest cause of this problem is DataReaders and returning these objects to the upper layers. Most likely some client class is not closing the DataReader it received from your DAL class, leaving the connection open/locked for who knows how long. Track down the DataReaders you're returning and make sure your client classes are closing/disposing of them properly.

I'd also start thinking about redesigning your data access layer by implementing Disposable pattern and possibly returning POCOs instead of Data (...Tables, ...Sets, ...Readers) objects.

Answer 3

Unfortunately I don't have my Data Access Layer and my business objects neatly sorted.

I do always use DataAdapters to dump my data directly into a DataTable, then manipulate from there.

The reason I know this because I have connections left open is that when I look at the stack trace for the error it's occurring when attempting to open a new connection.

Is there anyway to track connections in SQL server? Is my only solution doing a find all on .Open() to see where I might have forgotten a .Close() or to add error handling to close the connection on an exception?