Killing ProcessIDs using SMO
"Exclusive Access could not be obtained because the database is in use"
The above line should look familiar to anyone who has done a Database Restore and is easily solved if you are using Management
Studio or Query Analyzer. My personal favorite is to just kick the DB into single user mode, which drops any errant connections.
use master
go
alter database <dbname> set single_user with rollback immediate
alter database <dbname> set multi_user
I know some who prefer to explicitly drop the connections through the Activity Monitor and others who use a cursor based solution to cycle through the connections. To each his own...
I ran into this problem recently while using SMO to do a Database Restore. At my company, we have a process where we get an XML
file containing records dropped into a specific FTP folder from a 3rd party. I wrote a listener program that takes that XML file and
imports it into a staging DB. As part of the process, it does a backup of the DB and in the event of an import error, the DB gets
rolled back. This worked fine until I noted some errors in my log with the above "Exclusive Access" message, which led me on a
hunt to find out how to kill the processes using SMO.
After a bit of thinking (OK, and guessing...), I found 2 methods on the Server class itself. One takes a database name as an argument and kills all the running processes for that database.
The other one kills a single process by taking a processID as an argument.
public void KillAllProcesses(string
databaseName);
public void KillProcess(int
processId);
My modified code is below, with the "KillAllProcesses" line added. Hopefully this will be of help to someone else with the same problem.
internal void RestoreDatabase(string
backupFileName, string databaseName)
{
Restore
restore = new Restore();
restore.Database = databaseName;
restore.Devices.AddDevice(backupFileName,
DeviceType.File);
restore.ReplaceDatabase = true;
_server.KillAllProcesses(databaseName);
restore.Wait();
restore.SqlRestore(_server);
}