Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Set the DB to single_user mode first? –  FreshPhilOfSO Jan 31 '13 at 21:18
    
What spid(s) have a lock currently on the database? You can find this information from sys.dm_tran_locks. –  Thomas Stringer Jan 31 '13 at 21:26
    
@Phil: Have tried this - same issue. –  Nico Jan 31 '13 at 21:32
    
@ThomasStringer Will check this tomorrow when I have the VM accessable. –  Nico Jan 31 '13 at 21:33
    
Well, I'll clear up the mystery. Somebody is connected to the database (maybe even you?). Whenever you connect to a database (besides the master database) a shared lock will be granted on the database. This is so the database doesn't get dropped or restored while there are currently users. So, you need to find out the session and take action. –  Thomas Stringer Jan 31 '13 at 21:44
add comment

1 Answer

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;
share|improve this answer
    
Step 1: Works as intended. Step 2: shows multiple error-messages of the same type: object-object depends on collation. collation can't be changed, if a schemabound object depends on collation. remove those dependencies and retry. ( translated from german ). –  Nico Feb 1 '13 at 12:57
    
Running Processes: MSSMS and Report Server –  Nico Feb 1 '13 at 12:58
    
Sounds like you have other dependencies within your database. The process I define above simply resolves locking issues, but does not address object requirements for changing collation. I'd recommend posting the error message as part of your question or ask a second question, as this is unrelated to the question as currently posted. –  Mike Fal Feb 1 '13 at 16:51
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.