Monday, May 29, 2017

Find Isolation Level of transaction and Database

Find Isolation level of a transaction

SELECT session_id AS SessionID,
program_name AS ProgramName,
DB_NAME(database_id) AS DatabaseName,
CASE transaction_isolation_level
WHEN 0 THEN 'Unspecified'
WHEN 1 THEN 'ReadUncommitted'
WHEN 2 THEN 'ReadCommitted'
WHEN 3 THEN 'Repeatable'
WHEN 4 THEN 'Serializable'
WHEN 5 THEN 'Snapshot'
END AS Transaction_Isolation_Level
FROM sys.dm_exec_sessions

Changing the Transaction Isolation level
Using the below statement the isolation level of a transaction can be changed.