When run integration tests throw SqlException

Question

I am running integration tests. Before starting the test, I deleted the database and create it again. The first test is successful. But at the start of the second test I get an exception:

SetUp : System.Data.SqlClient.SqlException : Cannot drop database "Test" because it is currently in use.

Сode:

[TestFixture]
class Class1
{
    public SqlConnection Repository;

    [SetUp]
    public void LocInit()
    {
        Repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;");
        Repository.Open();
        Repository.Execute("USE master;");
        Repository.Execute("DROP DATABASE Test;");
        Repository.Execute("USE master; CREATE DATABASE Test;");
        Repository.Execute("USE Test;");
    }

    [Test]
    public void Test1()
    {
        using (var repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;"))
            repository.Execute("USE Test; SELECT 10");
    }

    [Test]
    public void Test2()
    {
        using (var repository = new SqlConnection(@"Data Source=.\SQLEXPRESS; Integrated Security=true;"))
            repository.Execute("USE Test; SELECT 10");
    }

    [TearDown]
    public void LocalTearDown()
    {
        Repository.Dispose();
    }
}

Why do I get this exception?

Is this across multiple fixtures? – Scott Chamberlain Sep 06 '16 at 22:01 — Scott Chamberlain, Sep 06 '16 at 22:01
Seeing 'master' in this scares the living hell out of me. – Ryan Intravia Sep 06 '16 at 22:06 — Ryan Intravia, Sep 06 '16 at 22:06

score 0 · Answer 1 · edited May 23 '17 at 12:18

0

The reason why you encounter such issue is that there are still some pending connections that might have not yet been killed. One workaround would be to kill all connections as described here, before actually dropping DB:

ALTER DATABASE Test SET SINGLE_USER WITH ROLLBACK IMMEDIATE

Another would be to track down why there is such connection left. This thread shows how you can list all connections to your DB and get rid of it in proper way.

edited May 23 '17 at 12:18

Community

1
1

answered Sep 06 '16 at 22:14

Yakuza

3,237
2
21
18

This is a test database. There are no other users. I do not understand what may be some pending connections. – Alexey Anisimov Sep 06 '16 at 22:32
Well one of your tests may leave connection active, or DROP happens before server manages to terminate previous connections. It's never obvious if networking is involved. – Yakuza Sep 07 '16 at 07:22

score 0 · Accepted Answer · edited May 23 '17 at 10:29

0

Havent tried it myself but maybe you can try this:

Set Pooling=false in your connection string

or

Clear the pool before you delete the database: SqlConnection.ClearAllPools()

"Cannot drop database because it is currently in use". How to fix?

edited May 23 '17 at 10:29

Community

1
1

answered Sep 07 '16 at 06:27

JFM

753
12
16

When run integration tests throw SqlException

2 Answers2