Error changing collation

Error changing collation

If you try to change Collation in database and you get some error like this,

Msg 5030, Level 16, State 2, Line 1
The database could not be exclusively locked to perform the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'BI_CONVERTION' cannot be set to Latin1_General_CI_AI.

Try this solution:
You need to get the database to single-user mode. You may need to go through and kill all of the current sessions so that you can get it to single user mode. Once you do, you essentially have an exclusive lock on the database. At that point, you shoudln't haven't a problem changing the collation.

Another way to make it:

To alter the database state to restrict the access to the single user mode, first open the Microsoft SQL Server Management Studio, and connect to a SQL Server instance. Open the list of available databases belonging to the related instance in the Object Explorer window. Right click on the sql server database that you want to set its mode to single user and select Properties in the context menu.

When you click properties menu item, the following Database Properties screen will be displayed for the selected database.

Select the Options page from the list in the left side of the screen. If you scroll down the options list for State options, you will see Restrict Access database options with three options listed in the combo box.

Restrict Access modes : Multiple, Single and Restricted modes.

If you select Single mode and click the OK button, you can either alter the database access mode to single user successfully or you will fail to change the access mode because of existence of active open connections to the Works database. The Management Studio IDE will prompt you to close all other connections to the related database for a successfull alter database option process.

To change the database properties, SQL Server must close all other connections to the database. Are you sure you want to change the properties and close all other connections?

After the alter command runs successfully, the database XXX will be displayed as shown in the Object Explorer window.

If an active connection exists other than the Management Studio, and you click the database XXX within the SQL Server Management Studio, the following warning message will be displayed:

The database XXX is not accessible. (ObjectExplorer)

If you right click on the database XXX, the following error message will be displayed.

Database 'XXX' is already open and can only have one user at a time. (Microsoft SQL Server, Error: 924)

After a database is altered as single user mode, it can be altered again back to multi user mode by running the below sql command.

ALTER DATABASE [XXX] SET MULTI_USER WITH NO_WAIT