AEDBin this case).
You can apply the change and rollback any active transactions at the same time by running:
ALTER DATABASE AEDB SET READ_COMMITTED_SNAPSHOT ON WITH ROLLBACK IMMEDIATE GOAnother extract from this URL
Having needed to enable it on an active application with active database connections in the past, I’ve found that option 2 works well as long as you do it during off-hours
Here’s a script that can do this (using
AEDB again as example):
-- Switch over to master to avoid hanging connection problems USE master GO /** * Cut off live connections * This will roll back any open transactions after 30 seconds and * restricts access to the DB to logins with sysadmin, dbcreator or * db_owner roles */ ALTER DATABASE AEDB SET RESTRICTED_USER WITH ROLLBACK AFTER 30 SECONDS GO -- Enable RCSI for AEDB ALTER DATABASE AEDB SET READ_COMMITTED_SNAPSHOT ON GO -- Allow connections to be established once again ALTER DATABASE AEDB SET MULTI_USER GO -- Check the status afterwards to make sure it worked SELECT is_read_committed_snapshot_on FROM sys.databases WHERE [name] = 'AEDB' GOIf that last
1then you are done