ALTER DATABASE SET READ_COMMITTED_SNAPSHOT is running forever
By: Date: April 16, 2017 Categories: READ_COMMITTED_SNAPSHOT

When working with a busy database you might run into problems when you want to change a database setting which requires the database to be in a consistent state, meaning no uncommitted transactions and no schema or table locks. Ideally the application(s) using the database should be offline for such a change but for cases where this cannot be guaranteed, it might happen that the SET READ_COMMITTED_SNAPSHOT command will run for hours or not finish at all. In this case the following suggestions might be helpful.

Using NO_WAIT
When using the NO_WAIT clause the command will probably (but not always) fail immediately if the change cannot be done immediately.

ALTER DATABASE TestDB1 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO


Avoiding deadlocks
Yes deadlocks may appear while enabling READ_COMMITTED_SNAPSHOT. To avoid this you may set a higher deadlock priority for your session before executing the command:

SET deadlock_priority high
GO
ALTER DATABASE TestDB1 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO

By enabling a higher priority when it comes to a deadlock, your session will survive and the other session will be terminated unless it has also deadlock_priority set to high.


Setting the database to SINGLE_USER mode
Before enabling SINGLE_USER mode make sure that you are the one using the database otherwise an application which is trying to connect every second to the database might lock you out.

USE TestDB1
GO
ALTER DATABASE TestDB1 SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TestDB1 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE TestDB1 SET MULTI_USER
GO


Taking the database offline
If nothing else works you will need to take the database offline before doing the change.

USE master
GO
ALTER DATABASE TestDB1 SET OFFLINE WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE TestDB1 SET READ_COMMITTED_SNAPSHOT ON WITH NO_WAIT
GO
ALTER DATABASE TestDB1 SET ONLINE
GO

By using the suggestions above you can make sure that the SET
READ_COMMITTED_SNAPSHOT will complete faster.

Leave a Reply

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