Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I've been writing some test code for a program I'm developing. Some of my code performs operations that modify data on a SQL Server. In order to avoid cluttering my database with test data, I use transactions. I run each test within a transaction and then roll back the transaction.

However, when I run my test code, it sporadically throws exception that it could not connnect to the SQL Server. Even worse, it seems that sometimes, the transactions just float in the aether, locking tables in my database so that ordinary users cannot connect. (This is a development environment database, but even still, it's a big hassle).

My tests are divided into about 100 tests; each test runs in it's own transaction; the tests run in sequence (or at least I think they should :-/).

If I run the tests one at a time, they always pass. I only have trouble when running all 100 tests at once within Visual Studio 2008. Each test runs very quickly, so I think that the problem has something to do with the SQL Server not being able to clean up the connection from a previous test and make available a new connection.

Is there some adjustment to my SQL Server configuration that may remedy this problem?

share|improve this question
    
Could we please see your code? It may not be cleaning up correctly. (Did you post a question on SO about this same problem? If yes, please link to it.) –  Jon Seigel Jun 21 '10 at 15:27
    
Yes, I did post a question of SO about this same problem. I did post the code of SO since SO is a programming forum. I felt that I might get an answer from a different point of view here. stackoverflow.com/questions/3072986/… –  Rising Star Jun 21 '10 at 15:30
1  
The comment over in that question is a good one -- make sure your transactions are either committed or rolled back every time you create one. This can definitely cause problems with locking. –  Jon Seigel Jun 21 '10 at 15:44
    
I've been looking over that. In the case of test code, the transactions should always be rolled back and never committed, which I believe I'm doing correctly. I think this may have something to do with the way the server makes connections available based on the fact that the test code runs fine whenever the tests are run one at a time, but sometimes fails when the tests are run all at once. –  Rising Star Jun 21 '10 at 15:49
add comment

1 Answer

up vote 2 down vote accepted

Are disposing of the connection object correctly after every call? It may be possible the connection pool is filling up and preventing further connections.

share|improve this answer
    
I thought I was disposing the connection properly because I used the SqlHelper class in my code. However, it turns out that there was a SqlDataReader object in the code under test that wasn't closed properly. Adding a single line of code to close the SqlDataReader fixed the problem; so it appears that there is nothing wrong with the server or its configuration; this problem was entirely programmatic in nature. –  Rising Star Jun 21 '10 at 19:25
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.