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.

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.

Comments 1

  1. Great cheat sheet you have put together here. Sometimes it is more hlepful to new SQL users to see an explanation in this format than reading the books online pages. Well done.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.