Drop all active database connections failed for Server when executing KillAllProcesses

Question

I need to perform a database restore from my application. Before doing this, I want to kill all processes as follows:

    private void KillAllProcessesOnSMARTDatabases(Server targetServer)
    {
        targetServer.KillAllProcesses(SMART_DB);
        targetServer.KillAllProcesses(SMART_HISTORY_DB);
        targetServer.KillAllProcesses(SMART_METADATA_DB);
        SqlConnection.ClearAllPools();
    }

However, when the first KillAllProcesses is run, I get the following exception:

Microsoft.SqlServer.Management.Smo.FailedOperationException: Drop all active database connections failed for Server 'MYServer'. ---> Microsoft.SqlServer.Management.Common.ExecutionFailureException: An exception occurred while executing a Transact-SQL statement or batch. ---> System.Data.SqlClient.SqlException: Only user processes can be killed.

The connection string used to create the server has sa credentials, however, the processes that need to be terminated are started under a different user. I tested the similar scenario and the test succeeded.

This started happening only recently. To me it appears there are some processes running that are not started by the user?

Discomposure

Answer 1

It would appear that your code is attempting to terminate all SQL Server Processes, which is not a good idea.

If you want to perform a database restore, you should set the database in question into either single_user mode or RESTRICTED_USER mode, the later being the most suitable.

Take a look at the following example of switching a database to RESTRICTED_USER mode and how to close any open user connections in the process.

How to: Set a Database to Single-User mode

Chole

Answer 2

You can use SMO to "kill" a particular database. This will force a drop of all client connections to that database only and then drop the database itself.

Microsoft.SqlServer.Management.Smo.Server oServer = this.GetSmoServer();
oServer.KillDatabase(this.DatabaseName);

Almire