Enable Snapshot Isolation

The statements to enable snapshot isolation on a database and monitor active snapshot transactions.

Published Sep 6, 2007

Enabling snapshot isolation in two steps: first ALLOW_SNAPSHOT_ISOLATION ON so applications can opt in per-connection, then READ_COMMITTED_SNAPSHOT ON to make it the default for everyone. The final query shows how to watch active snapshot transactions while you roll it out. (Enable both during a quiet window - they require single-user access momentarily.)

ALTER DATABASE <database_name>
SET ALLOW_SNAPSHOT_ISOLATION ON
GO

ALTER DATABASE <database_name>
SET READ_COMMITTED_SNAPSHOT ON
GO

SELECT * FROM sys.dm_tran_active_snapshot_database_transactions
GO