Why is Sql Server database busy?

Question

I'm trying to create a database, run some tests on it, then drop the database:

namespace TestConsole
{
    class Program
    {
        static void Main(string[] args)
        {
            const string testConnectionString = @"Server=localhost\SQL2014EXPRESS64; Database=RepoTest; Trusted_Connection=True;";
            const string masterConnectionString = @"Server=localhost\SQL2014EXPRESS64; Database=master; Trusted_Connection=True;";

            // connect to master and create RepoTest db
            using (var connection = new SqlConnection(masterConnectionString))
            using (var command = new SqlCommand("CREATE DATABASE RepoTest", connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
            }

            // connect to RepoTest and create some new tables
            using (var connection = new SqlConnection(testConnectionString))
            using (var command = new SqlCommand("CREATE TABLE Test(ID INT NOT NULL)", connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
            }

            // connect to master and delete RepoTest db
            using (var connection = new SqlConnection(masterConnectionString))
            using (var command = new SqlCommand("DROP DATABASE RepoTest", connection))
            {
                connection.Open();
                command.ExecuteNonQuery();
            }
        }
    }
}

but I get this Exception when trying to drop the test database:

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Cannot drop database "RepoTest" because it is currently in use.

But I expected it to no longer be in use as the part that was using that database was in a using block so I expected the connection to be closed and the operation to be complete, if this isn't the case is there a command I can use to drop the database when its finished its current process?

Just for kicks and giggles, put a `Thread.Sleep(1000)` just before the third section. — gunr2171, Jul 08 '14 at 17:34
Have a look [here](http://stackoverflow.com/questions/7469130/cannot-drop-database-because-it-is-currently-in-use) and [here](http://stackoverflow.com/questions/14069661/dropping-sql-server-database-through-c-sharp) for some ideas. — HABO, Jul 08 '14 at 17:34
Try adding `pooling=false` to your connection strings to disable connection pooling. — D Stanley, Jul 08 '14 at 17:36
Have you considered creating tables and other objects in `tempdb` instead? — Dave Mason, Jul 08 '14 at 17:45

score 0 · Accepted Answer · answered Jul 08 '14 at 17:47

0

Your connection object using the testConnectionString will use pooling.
Disable pooling on that connection string by adding Pooling=false:

const string testConnectionString = @"Server=.; Database=RepoTest; Trusted_Connection=True;Pooling=false;";

answered Jul 08 '14 at 17:47

Magnus Johansson

28,010
19
106
164

Why is Sql Server database busy?

1 Answers1