server.Databases[databaseName].Drop(); fails in MSTest

Question

The following exception is thrown:

Error Message: Microsoft.SqlServer.Management.Smo.FailedOperationException: Drop failed for Database '4d982a46-58cb-4ddb-8999-28bd5bb900c7'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Cannot drop database "4d982a46-58cb-4ddb-8999-28bd5bb900c7" because it is currently in use.

Any idea what caused this?

Is it possible to call a SMO function to finalize any running Transact-SQL statements?

Answer 1

As a guess, I wonder if your connection pool is counting against you - i.e. you have previously executed some commands on a SqlConnection that is now being kept around for re-use. You could try disabling pooling? (pooling=false in the connection string, IIRC).

Also - have you been careful to close all your commands/connections/readers etc? Ideally via using? As a last resort, you could try using T-SQL to kill all the other spids from your machine - hacky, but it might work.

Answer 2

Use the Server.KillDatabase(string database) method which closes all open connections for you.