Settings for SQL Server committed read Snapshot isolation level

If you want to set the SQL Server database transaction isolation level to committed read-only snapshot isolation

If you run the following statement directly:

ALTER Database [Mydbname] SET read_committed_snapshot on

may be blocked for a long time. My side has not been executed for 4 hours in the formal environment test.

You can choose to run the following statement:

if(charindex('Microsoft SQL Server 2005',@ @version)> 0)  begin      Declare @sql varchar(8000)      Select @sql = 'ALTER DATABASE' + db_name()+ 'SET single_user with ROLLBACK IMMEDIATE; ALTER DATABASE' + db_name()+ 'SET read_committed_snapshot on; ALTER DATABASE' + db_name()+ 'SET Multi_user;'        Exec(@sql)  End  

The test script completes within 1 minutes.

is_read_committed_snapshot_on Field of sys.databases query

Select  from where = db_name ()  

is_read_committed_snapshot_on if 1 represents the currently committed read Snapshot isolation level.

Citation URL: http://www.it118.org/Specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/a25f60b1-697d-4651-b4e4-e7e1598feced.htm