Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

WaterOx SQL

Chris Bell (MCITP) has been working with Microsoft technologies for over 20 years and using SQL Server for over 18 of those years. He is currently the Owner and Lead consultant for WaterOx Consulting, a Maryland based company focused on helping clients, that can’t afford or justify a full time data team, get the most from their SQL Server environments. For more information please visit WateroxConsulting.com.

Gain exclusive control of a database

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.

Comments

Leave a comment on the original post [wateroxconsulting.com, opens in a new window]

Loading comments...