This knowledge article may contain information that does not apply to version 21.05 or later which runs in a container environment. Please refer to
Article Number 000385088 for more information about troubleshooting BMC products in containers.
Legacy ID:KA357223
Microsoft SQL Server provides the “snapshot” isolation level, which allows a transaction to read the last committed version of the data that is currently being changed. Thus, the transaction's view of the data is consistent with the state of the data when the transaction began without being blocked by other transactions. This reduces the possibility of deadlocks.
WARNING
Some processing overhead occurs due to the creation of a temporary database with a large size, and the storage and retrieval of versioned data. For more information about the snapshot isolation in SQL Server, go to https://msdn.microsoft.com/en-us/library/ms130975.aspx
To use a snapshot isolation level with the AR System database
1 Stop the AR System server to ensure that all connections to the AR System database are closed.
For a server group or a shared database, stop all of the AR System instances.
2 Verify whether snapshot isolation has been enabled for the AR System database:
SELECT snapshot_isolation_state FROM sys.databases where name = 'ARSystem'
Microsoft SQL Server 2005 does not enable snapshot isolation for a database by default.
3 To enable snapshot isolation, enter:
ALTER DATABASE ARSystem SET ALLOW_SNAPSHOT_ISOLATION ON
4 To verify whether the appropriate isolation level has been set for the AR System database, enter:
SELECT is_read_committed_snapshot_on FROM sys.databases where name = 'ARSystem'
5 To set the snapshot isolation level, enter:
ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT ON
6 Restart the AR System server.
Optionally, to revert to the original setting, use the following commands in the same sequence as mentioned:
ALTER DATABASE ARSystem SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE ARSystem SET ALLOW_SNAPSHOT_ISOLATION OFF