SQL Server Vs. Express Behaviour Difference...

Hi all,

I want to share an experience I made in the last few days and like to hear your comments about it. I am developing an ASP.Net 2.0 Web Application using SQL Server 2005 on my local system. After implementation was done, I had to deploy the application to the production server. Because of license problems, on the server is the express edition of SQL Server installed. The system worked fine for about 2 month. But the last week we noticed, that there was deadlocks in the application. After searching a while I noticed, that there were a lot of open connections. When you open SQL Server Management Studio and look at Management > Activity Monitor, you can see all opened connections in the connection pool.

So the problem was, that with every request, a new connection was created, instead of using the existing ones, even if the state of the connections was sleeping. On SQL Express, if a specific limit of connections is reached, it'll wait for a connection to be released, but there is no release, so it threw a timeout error. But suprisingly, on SQL Server there also were a lot of connections created, but there were never a deadlock, which I can't explain. Also I can't explain, why it also worked for 2 months on SQL Express.

The architecture:

I have data classes, which are implementing IDisposable. In the dispose method, I call Dispose on the connection and set it to null. And in code I instanciate my data classes in using blocks. So on reaching the end of the using block the data class instance is disposed. In the dispose method the connection is disposed. So I thought, that everythink will work fine, but it doesn't.

The problem was solved by calling Close() on the connection in the Dispose method in my data class just before calling conn.Dispose().

So does this make sense to you? The fact, that it solved my problem lets me believe to that solution, but I can't really say why. So if you have any ideas or knowledge, I'd love to hear it.

Regards,

Koray