CJSmith dot me

I dump stuff I find useful here

Apply ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to SQL Server

Published / by Chris Smith / Leave a Comment

We needed to enable ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to specific databases on a SQL Server 2008 instance, I found to do this even with users off I was being shown errors stating it must be done in Single User Mode.
I decided I wanted to do this as quickly as possible so slapped together some SQL code.

--Set Db to single user mode, rollback if any process still connected
ALTER DATABASE "<Database>" SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
ALTER DATABASE "<Database>" SET ALLOW_SNAPSHOT_ISOLATION ON
GO
ALTER DATABASE "<Database>" SET READ_COMMITTED_SNAPSHOT ON
GO
--Set Db to multi user mode
ALTER DATABASE "<Database>" SET MULTI_USER
GO

--Check options are enabled (Value=1) or disabled (Value=0)
SELECT snapshot_isolation_state_desc FROM sys.databases where name="<Database>"
SELECT is_read_committed_snapshot_on FROM sys.databases where name="<Database>"

Source: Microsoft MSDN
Microsoft MSDN
I am pretty sure the Check options are enabled must have been someone else’s code, when I find where I got this I will add a further credit.

Leave a Reply