Search this blog

Tuesday, May 22, 2012

Isolation Level Of Current Database

Following SQL Code-snippet will helps to find out What is the Isolation Level is configured for Current Database,

SELECT CASE transaction_isolation_level
      WHEN 0 THEN 'Unspecified'
      WHEN 1 THEN 'ReadUncomitted'
      WHEN 2 THEN 'Readcomitted'
      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

The above query will return the Isolation of of current DB. There are many ways to find out this settings. 
You can find it out by using DBCC command as well,
DBCC USEROPTIONS
After execute the given DBCC Command, look at the value of "isolation level" in the result, refer the exhibit given below:-