KDE Rocks!

entries RSS comments RSS

Troubleshooting the database could not be exclusively locked to perform the operation error on SQL Server Written on February 20, 2010, by Mehmetali Shaqiri.

Recently we have started to implement our own Content Management System codename  DeepThought and when testing the creation of articles and menus we realized that we cannot use the default database collation of SQL Server. The reason is that in future we might have clients that require the use of Cyrillic characters.  Therefore the collation that should be used for these cases is SQL_Latin1_General_CP1251_CS_AS.

But when trying to change the collation I came across the following error:

The database could not be exclusively locked to perform the operation.

Apparently you cannot change the database collation if the accessibility of the database is set to MULTI_USER (which is by default).

Therefore if you want to change the database collation, you must do the following:

• Set the restrict access of the database to  SINGLE_USER (note the you might kill all the active sessions)
• Change the collation of database to whatever you want it, in my case SQL_Latin1_General_CP1251_CS_AS
• And switch back the restrict access of the database to MULTI_USER

You can accomplish this either by executing the following t-sql in you sql management studio:

ALTER DATABASE [database_name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [database_name] COLLATE [desired collation]
ALTER DATABASE [database_name] SET MULTI_USER

or from SQL Management Studio by choosing the database properties:

Sometimes the simplest issues can cause you a real headache.

© Copyright Phalanx Blogosphere - Powered by Wordpress - Phalanx logo is designed by Leopard Cana