How do you find current database's transaction level on SQL Server?
How do you find current database's transaction level on SQL Server?
Run this:
SELECT 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
where session_id = @@SPID
DECLARE @UserOptions TABLE(SetOption varchar(100), Value varchar(100))
DECLARE @IsolationLevel varchar(100)
INSERT @UserOptions
EXEC('DBCC USEROPTIONS WITH NO_INFOMSGS')
SELECT @IsolationLevel = Value
FROM @UserOptions
WHERE SetOption = 'isolation level'
-- Do whatever you want with the variable here...
PRINT @IsolationLevel
If you are talking about the current transaction nesting level, then you would use @@TRANCOUNT.
If you are talking about transaction isolation level, use DBCC USEROPTIONS and look for an option of isolation level. If it isn't set, it's read committed.
SELECT CASE
WHEN transaction_isolation_level = 0 THEN 'Unspecified'
WHEN transaction_isolation_level = 1 THEN 'Read Uncommitted'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'OFF' THEN 'Read Committed'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 1 THEN 'Snapshot Read Committed'
WHEN transaction_isolation_level = 2 AND d.snapshot_isolation_state_desc = 'ON' AND d.is_read_committed_snapshot_on = 0 THEN 'Snapshot'
WHEN transaction_isolation_level = 3 THEN 'Repeatable Read'
WHEN transaction_isolation_level = 4 THEN 'Serializable' END AS TRANSACTION_ISOLATION_LEVEL,
d.is_read_committed_snapshot_on,
d.snapshot_isolation_state_desc
FROM sys.dm_exec_sessions
CROSS JOIN sys.databases AS d
where session_id = @@SPID
AND d.database_id = DB_ID();