How can I set ALLOW_SNAPSHOT_ISOLATION ON to SQL Server without restarting the database server?

Question

I am trying to run the following statement in a SQL server database:

ALTER DATABASE myDB SET READ_COMMITTED_SNAPSHOT ON

The Query Analyzer just keeps spinning and returns nothing. I read about other people experiencing this problem, and that they should return normally fairly quickly, and the only way they were found around is to restart the SQL Server service. Unfortunately, I do not have such an option. Is there a way to get this statement to work without restarting the service?

Answer 1

, , . , , :

ALTER DATABASE myDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
ALTER DATABASE myDB SET READ_COMMITTED_SNAPSHOT ON;
ALTER DATABASE myDB SET MULTI_USER;