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?