error altering database collation

Question

Due to a database-restore, collation has been changed. If I try to

alter database <xyz> collate <xyz>

I get an error, that no exclusive lock to database to fulfil this operation can be gotten. Since sql-server is part of a virtual-machine, I'm quite sure, that no*one* has any database-access at this time but me. I thing that one ore few processes prevent me altering the collation having access to db, but I can't determine which it could be. I've deactivated all possible services having something in common with the sql-server, but error stays the same. ( Running Processes: MSSMS and Report Server )

I know I have successfully changed collation on another db in the past - easyway - via contextmenu/properties/collation, but this also has no success.

Reading several articles of changing collation of each object by one via cursor operation is not what i want to do.

Any hints would be appreciated!

Answer 1

Assuming that you have full control over the database and can kick everyone out/close all existing process that are connecting to that database, you can use the following statement to force the database into single user mode with you as the only connection, then make your change, then switch it back to multi user.

The T-SQL to do this is:

--Step 1
USE [foo];
GO;

ALTER DATABASE [foo] SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO;

--Step 2
ALTER DATABASE [foo] COLLATE <<Colation Name>>;
GO;

--Step 3
ALTER DATABASE [foo] SET MULTI_USER;
GO; 

If you want to be nice and identify the processes that are connected to that database first, you can get that information from sys.dm_exec_sessions:

USE [foo];

SELECT 
    session_id
    ,login_name
    ,host_name
    ,program_name
FROM
    sys.dm_exec_sessions
WHERE
    database_id = DB_ID()
    AND session_id != @@SPID;