8

Busy with automated test cases in C#, and we need to restore a DB snapshot after every test. Problem is, when running multiple tests it fails because the "Database state cannot be changed while other users are using the database."

We use SqlConnection.ClearAllPools(); before the restore, but after the fourth test it won't restore again for the run, and it seems that the pools stop clearing. (Why four? See edit 2)

How can I make this more reliable?

EDIT

Maybe I should give more information about the system. These are unit integration tests for a service. The tests reference the service dll (no service reference, we access the service methods directly). So, there are no SQL in the tests other than restoring the DB snapshot after every test block.

This is a service, so we don't exactly manage the connections. We have a central point where we create the Database objects from which we get our DbCommands to execute stored procedures.

Using sp_who2 within SQL studio, I observe the following: There is one session to the DB for the first four test blocks (where each block is separated by a ClearAllPools() and a snapshot restore), but as of the fifth test block there are three sessions against it. (Why? This might be a clue to the problem.) (The snapshot restore opens an additional connection to the master DB.) All open connections have status sleeping, awaiting command, even the one blocking the snapshot restore connection.

EDIT 2

Why five? I thought the test cases would execute in random, but I was wrong. I found the culprit. More than one connection opens, the system only uses the last one, and the others are left hanging and won't clear until you exit.

Peet Brits
  • 2,911
  • 1
  • 31
  • 47
  • 1
    What are you doing to manage your SQL Connections in the code under test? – Stealth Rabbi Dec 13 '11 at 12:54
  • Stealth Rabbi: Not sure what you mean by manage, but this is a service, so probably nothing. I updated my question with more information. – Peet Brits Dec 13 '11 at 13:40
  • I mean like how are you opening / closing your connections and transactions. Also, if you're using the real database, these are really integration tests. – Stealth Rabbi Dec 13 '11 at 14:55
  • We just call e.g. db.ExecuteNonQuery(command, ...), and most individual calls are without transactions. This is per method, not global. (We have a framework to handle most generic cases, but it looks similar) – Peet Brits Dec 14 '11 at 06:27

3 Answers3

6

I use a different approach. I run the tests in a transaction that will be disposed (rollback) at the end of every test. This way you don't need to throw away the database on every test session because the db is always "clean". In C# you can create a TransactionScope and dispose it after the test, or (better) if you use xUnit.net you can use the AutoRollback attribute.

ema
  • 5,668
  • 1
  • 25
  • 31
  • With TransactionScope, must I still set the transaction on every sql command? This will be a problem, because the system is fairly big. – Peet Brits Dec 13 '11 at 13:04
  • Nope. You have various options, the best will be create the transaction in the Setup and dispose it in the TearDown. If you have a base class for your tests you could use that for managing the transaction lifecycle. – ema Dec 13 '11 at 13:34
  • CONS: TransactionScope requires me to start the "Distributed Transaction Coordinator" service, and I lose the ability to execute SQL code in studio while debugging (it's not committed due to the transaction). – Peet Brits Dec 13 '11 at 14:26
  • +1 for working answer, but I'm keeping it open for a while in case someone else knows the problem with the pools not closing. – Peet Brits Dec 14 '11 at 07:28
  • Well.. and what if you want to test how your application handles transactions? – Stefan Paul Noack Dec 14 '11 at 09:00
  • Transactions and testing were more like an after thought. I don't have the last say on this. Features for the deadlines take priority. I still have to convince them that we need tests to see what happens when things break. – Peet Brits Dec 14 '11 at 10:14
  • @PeetBrits The DTC is usually required for access to multiple databases, or multiple connections to the same database. It's possible to change the code to always use the same connection and you should be fine. – Christian Droulers Nov 19 '14 at 16:08
6

Before you restore your snapshot set the database in single user mode:

   ALTER DATABASE <mydb> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

If you don't wish to kill the open connections you can make the command wait by running it without the rollback option

   ALTER DATABASE <mydb> SET SINGLE_USER

It is a hack, but it is very hard to get your snapshot restore working consistently otherwise. (once bitten, twice shy.)

Filip De Vos
  • 11,568
  • 1
  • 48
  • 60
0

We're doing the same thing. Restore the DB from backup before every test:

ALTER DATABASE <dbname> SET OFFLINE WITH ROLLBACK IMMEDIATE
DROP DATABASE <dbname>
RESTORE DATABASE <dbname> FROM DISK= ...

For the case that the database doesn't exist yet we're also checking for that using

IF DB_ID (N'<dbname>') IS NOT NULL

Hope this helps

Stefan Paul Noack
  • 3,654
  • 1
  • 27
  • 38