AEDB
in 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
GO
Another 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' GO
If that lastSELECT
returned1
then you are done