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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 | /* 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.
Comments 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.