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.