asked on

Change Database Collation

Hello, I have a database that I incorrectly configured using a collation of SQL_LATIN_GENERAL1_CS_AS. Can someone please tell me how to change the collation to SQL_LATIN_GENERAL1_CI_AS? I need the change applied to all tables.

I see in the database properties, under Options, there is a selection to change it, but when I try it says the database cannot be used exclusively. I tried bringing it offline and then I couldn't get to the properties.

Also, the database is about 2 GB in size. Will it take a while to change it over? Thanks.

chapmandew

alter database dbname collate SQL_LATIN_GENERAL1_CI_AS

craigbtg

ASKER

The message I get is "The database could not be exclusively locked to perform the operation." This is the same message that I get when I try to change it using the database properties that I described. How do I lock it exclusively?

chapmandew

Is there a lot of transactions going on in the database? You may have to wait until night time when there are fewer users so you can set the collation.

craigbtg

ASKER

As a test, I tried it on a small database that is not being used at all and it gave the same message. There are no jobs or subscriptions running either that could be using it. I literally created a new database with the incorrect collation and then tried to switch it. There must be some way to lock it exclusively.

ASKER CERTIFIED SOLUTION

chapmandew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

craigbtg

ASKER

Fantastic...that worked! Any idea how long it could take to switch the collation on a database that is about 2GB with about 50 tables? Thanks for your help!

chapmandew

Not long at all....