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.

The post Gain exclusive control of a database appeared first on WaterOx Consulting, Inc.