Reliably drop a database on SQL Server 2008 via C#

Question

I am currently trying to drop a database via the following code

using Microsoft.SqlServer.Management.Smo;

var server = new Server(Server);
server.KillAllProcesses("Db");
server.KillDatabase("Db");

Sometimes it works, but others I get the following exception:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Kill database failed for Server 'Host1'.
---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch.
---> System.Data.SqlClient.SqlException: Changes to the state or options of database 'Db' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.

Any ideas of how to reliably drop the database via code?

Answer 1

To reliably delete the database via SMO it is sufficient to call server.KillDatabase("Db");. MSDN does state that KillDatabase drops active connections even if their examples are a bit misleading.

KillDatabase issues ALTER DATABASE [Db] SET SINGLE_USER WITH ROLLBACK IMMEDIATE and then DROP DATABASE. KillAllProcesses list all connections and issues one kill per process as a separate batch; assuming KillAllProcesses is asynchronous the exception is thrown when a kill happens after KillDatabase sets the DB to the single-user mode.