Running out of DB connections using LINQ to SQL

Question

In developing a relatively simple web service, that takes the data provided by a post and records it in a database table, we're getting this error:

Exception caught: The remote server returned an error: (500) Internal Server Er or. Stack trace: at System.Net.HttpWebRequest.GetResponse()

on some servers, but no others. The ones that are getting this are the physical machines, the others are virtual, and obviously the physical servers are far more powerful.

As far as we can tell, the problem is that the DB connections aren't being released back to the pools after each query. I'm using the using pattern below:

                using (VoteDaoDataContext dao = new VoteDaoDataContext())
                {

                    dao.insert_response_and_update_count(answerVal, swid, agent, geo, DateTime.Now, ip);
                    dao.SubmitChanges();
                    msg += "Thank you for your vote.";
                    dao.Dispose();
                }

I added the dao.Dispose() call to ensure that connections are released when the method finishes, but I don't know whether or not it's necessary.

Am I using this pattern correctly? Is there something else I need to do to ensure that connections get returned to the pools correctly?

Thanks!

Answer 1

Your diagnostic information is not good enough. An HTTP/500 isn't enough detail to really tell if your theory is correct. You're going to need to capture a full stack trace in your logging if you want to get to the problem. I think you've jumped to a conclusion here. And no, you do not need that Dispose() before the end of your using{} block. That's what using{} does.

Answer 2

I thought that dispose() call was redundant, but I wanted to be sure.

We're seeing the connection pools saturating in the SQL logs (I can't look at the directly, I'm just a developer, and this stuff's running in a prod environment), and my ops guy said he's seeing connections timing out... and once they time out, the server starts running again, until the next time it saturates the connection pool.

We're going through the process of tweaking the connection pool settings at the moment... I wanted to be certain that I wasn't doing anything wrong, since this is my first time using Linq.

Thanks!