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:

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:

Database Properties

Sometimes the simplest issues can cause you a real headache.

  • yasin

    thanks for you give inform.

  • belle

    thanks for sharing, helped me a lot :)

  • haroon

    Thanks, your article was helpful for me. cheers

  • http://www.sangayrehberi.com Dincer

    Thank you very much for t-sql.. It just worked for me :)

  • MJ

    Thanks a lot…It was very helpful.

  • Niels Brinch

    Thanks! This helped me – the problem with doing it through the UI is that the options window might be opened in a different connections and then you don’t get the exclusive lock.

  • Livnow

    Thanks. it worked. Keep up the good work, you are helping a great many people

  • Danial Kaviani

    Thanks a lot Man. it is very helpful for me.

  • Fco. Gutierrez

    thanks a lot

blog comments powered by Disqus

11,333 views

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