SqlConnection/SqlCommand keeps database in use after Close and Dispose

Question

I am trying to create a temporary database for the purpose of integration testing in Xunit, but when I try to drop the temporary database, I get an error:

Cannot drop database "TempDatabase_[numbers]" because it is currently in use.

Simply closing and disposing the command and connection does not seem to cut it.

This is a trimmed-down version of my test that is failing:

using System;
using System.Data.SqlClient;
using Xunit;

namespace Test
{
    public class Test_Raw_Spec
    {
        [Fact]
        public void PerformWorkInTemporaryDatabase()
        {
            string connectionStringTemplate = "Data Source=SQLEXPRESS;Initial Catalog={0};Integrated Security=SSPI;Connection Timeout=10";
            int dbNum = (new Random()).Next() % 1000000;
            int tblNum = (new Random()).Next() % 1000000;

            string nameTempDb = $"TempDatabase_{dbNum}";
            string nameTempTable = $"TempTable_{tblNum}";

            var sqlConnection1 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand1 = new SqlCommand($"CREATE DATABASE {nameTempDb}", sqlConnection1);
            sqlConnection1.Open();
            sqlCommand1.ExecuteNonQuery();
            sqlCommand1.Dispose();
            sqlConnection1.Close();
            sqlConnection1.Dispose();

            var sqlConnection2 = new SqlConnection(string.Format(connectionStringTemplate, nameTempDb));
            var sqlCommand2 = new SqlCommand($"CREATE TABLE {nameTempTable}(id int)", sqlConnection2);
            sqlConnection2.Open();
            sqlCommand2.ExecuteNonQuery();
            sqlCommand2.Dispose();
            sqlConnection2.Close();
            sqlConnection2.Dispose();

            var sqlConnection3 = new SqlConnection(string.Format(connectionStringTemplate, "master"));
            var sqlCommand3 = new SqlCommand($"DROP DATABASE {nameTempDb}", sqlConnection3);
            sqlConnection3.Open();
            sqlCommand3.ExecuteNonQuery();
            sqlCommand3.Dispose();
            sqlConnection3.Close();
            sqlConnection3.Dispose();
        }
    }
}

Mr.Brownstone · Accepted Answer · 2020-12-20T20:13:13.067

This is because of Connection Pooling. When you close and dispose of the connection it is released back to the pool ready to be used again instead of being destroyed. Creating and destroying connections over and over again is a very expensive process and so connection pools are used in an attempt to improve the overall performance of your application. Connections are destroyed when they are finalised (when the pool is recycled or restarted such as when your application starts up or shuts down.)

Additionally, you could be more efficient in your usage of command and connections. You can change the text of a command if it is done executing. If you don't want to do this you could at least reuse the connection:

private void Execute()
{
    using (var connection = new SqlConnection("."))
    {
        connection.Open();

        using (var command = connection.CreateCommand())
        {
            command.CommandText = "CREATE DATABASE [test]";
            command.ExecuteNonQuery();

            connection.ChangeDatabase("test");
            command.CommandText = "CREATE TABLE [dbo].[MyTable] (id int)";
            command.ExecuteNonQuery();
            
            // you must change your db context to drop the database
            connection.ChangeDatabase("master");
            command.CommandText = "DROP DATABASE [test]";
            command.ExecuteNonQuery();
        }
    }
}

The pool is implemented in the SqlClient library. You get one pool per unique connection string and is not related to the developers use of the 'using' statement. — Mr.Brownstone, Dec 20 '20 at 20:11
Yep, you are correct. I realised just after I typed the comment and so deleted it... — coolblue2000, Dec 20 '20 at 20:14
`ALTER DATABASE [test] SET SINGLE_USER WITH ROLLBACK AFTER 5` will force all other connections off the db after 5s (eg. other connections in the pool). — Richard, Dec 20 '20 at 20:34
I found out you can also add `Pooling=false` to your connection string, which ends up also clearing the connection pool (see https://stackoverflow.com/questions/7004701/cannot-drop-database-because-it-is-currently-in-use-how-to-fix/22390350). Since Integration Testing is supposed to be a smaller subset of all your tests, this would have been a viable alternative for me as well. — VeeTheSecond, Dec 21 '20 at 02:15

score 1 · Answer 2 · answered Dec 20 '20 at 19:53

Have you tried waiting for a short time before dropping the db? It could well be that the connection has not managed to close in time.

Also you do not need to close the connection, calling dispose will do it all for you anyway. Plus it is best to do this in a using statement

using(var sqlConnection1 = new SqlConnection(string.Format(connectionStringTemplate, "master")))
{
//do your stuff here
}

This way you do not have to worry about closing anything as it will automatically do so at the end of the using block.

I also would not recommend opening loads of connections. One connection will suffice as opening connections is expensive. So create one (or two in your case) and then reuse it for each command.

Adding a 120 second delay between connections 2 and 3 (`System.Threading.Thread.Sleep(2 * 60 * 1000);`) does not seem to help. I like your other suggestions, and will implement them once I get a baseline working. — VeeTheSecond, Dec 20 '20 at 20:05
try calling sqlConnection2.ClearAllPools() before dropping the db — coolblue2000, Dec 20 '20 at 20:08

SqlConnection/SqlCommand keeps database in use after Close and Dispose

2 Answers2