Deleting MS SQL 2008 DB - error: "Only user processes can be killed"

Question

Hello,

does someone encountered a problem with deleting MSSQL 2008 DB where WSP server is returning this error:

[1/13/2011 11:29:10 AM] ERROR: 'Microsoft SQL Server 2008' DeleteDatabase
System.Data.SqlClient.SqlException: Only user processes can be killed.

?

Thanks

Answer 1

Hello,

I've managed to solve this issue by running this two queries:

ALTER DATABASE [DBNAME] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [DBNAME] SET MULTI_USER WITH ROLLBACK IMMEDIATE

Can someone comment if this is the right way to do this? Yes it has solved my problem and I'm wondering if this could be added to WSP sources.

Answer 2

I've also had this problem with restoring database. Running mentioned queries before database restore also fixed this problem. No one else had experienced this?

EDIT: I think below lines should be added to WSP by default:

ExecuteNonQuery(String.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", database.Name));
ExecuteNonQuery(String.Format("ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE", database.Name));

to private void RestoreFromBackup(SqlDatabase database, string[] files) function

and

ExecuteNonQuery(String.Format("ALTER DATABASE [{0}] SET SINGLE_USER WITH ROLLBACK IMMEDIATE", databaseName));
ExecuteNonQuery(String.Format("ALTER DATABASE [{0}] SET MULTI_USER WITH ROLLBACK IMMEDIATE", databaseName));

to public virtual void DeleteDatabase(string databaseName) function in MsSqlServer.cs file. They don't do anything harmfull but as I've wrote before it has fixed my problem.