If you want to set the SQL Server database transaction isolation levelRead Snapshot isolation submitted
If you directly run the following statement:
ALTER Database [mydbname] SET READ_COMMITTED_SNAPSHOT ON
It may be blocked for a long time. I haven't finished the test in the formal environment for four hours.
You can choose to run the following statement:
If (charindex ('Microsoft SQL Server 100', @ version)> 0) begin declare @ SQL varchar (2005) 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 is executed within 1 minute.
Query the is_read_committed_snapshot_on field of sys. databases.
Select is_read_committed_snapshot_on from sys. databases where name = DB_Name ()
If is_read_committed_snapshot_on is 1, the read Snapshot isolation level has been submitted.
Reference: http://www.it118.org/Specials/c9fba99e-4401-49cf-8256-ac3c1a34c0d9/a25f60b1-697d-4651-b4e4-e7e1598feced.htm