Last night a client tried to ruin a script to enable Read_Committed_snapshot on a SQL database. There were so many connections that kept popping up, even after restarts, that they were not able to change the setting.
As a fix I provided the script below.
Basically it uses the active SSMS session to change the database to single_user mode with a 60 second grace period for existing connections to finish up anything. It then applies the change script and does a quick select to check the change did apply. It then restores the database to a multi-user state so things go back to normal.
/* Disconnect all users and leave current session as single user. Allows 60 seconds for connections to complete transaction, after 60 seconds it will roll any open transactions back. */ ALTER DATABASE Your_DB SET SINGLE_USER WITH ROLLBACK AFTER 60 /*In this sample I enable read_committed_Snapshot */ ALTER DATABASE Your_DB SET READ_COMMITTED_SNAPSHOT ON; GO /* Check that change was applied */ SELECT database_id, name, is_read_committed_snapshot_on FROM sys.databases /*restore database to multi-user mode after change made.*/ ALTER DATABASE Your_DB SET MULTI_USER GO
This script now has a permanent spot in my personal DBA reference scripts folder since just changing the middle section of this script lets me use it as a ‘template’ for any other times I come up against a similar situation.