Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Drop all active database connections failed for Server when executing KillAllProcesses

Tags:

sql-server

smo

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?

like image 489
laconicdev Avatar asked Sep 17 '09 14:09

laconicdev


1 Answers

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

like image 74
John Sansom Avatar answered Nov 19 '22 05:11

John Sansom