SQL Server 2005 introduces a new snapshot isolation level to enhance concurrency for OLTP applications. In earlier days concurrency was based on frequent locks of rows, pages and tables. snapshot isolation in contrast uses row versioning to avoid these frequent lock situations.

To enable snapshot isolation you have to execute the folwoing two statements on your server:

ALTER DATABASE database_name SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE database_name SET READ_COMMITTED_SNAPSHOT ON

The first statement generally enables snapshot isolation on your database, the second one enables committed reads by using snapshot isolation on all connection that will follow as long as this database option is set.

Note: the second statement can only be executed when there is no other concurrent connection except of your own to the SQL Server. Therefore you first have to close all other connections to the server.

To verify that snapshot isolation is correctly enabled you can look at the sys.database table at the columns snapshot_isolation_state (should be 1 for enabled) and is_read_commited_snapshot_on (should also be 1 for enabled):

SELECT * FROM sys.databases