How to find current transaction level

Question

How do you find current database s transaction level on SQL Server

User · Answer

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.

User · Answer

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   docs microsoft com reference for the constant values

User · Answer

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

User · Answer

SELECT CASE             WHEN transaction isolation level   1               THEN  READ UNCOMMITTED             WHEN transaction isolation level   2                 AND is read committed snapshot on   1               THEN  READ COMMITTED SNAPSHOT             WHEN transaction isolation level   2                 AND is read committed snapshot on   0 THEN  READ COMMITTED             WHEN transaction isolation level   3               THEN  REPEATABLE READ             WHEN transaction isolation level   4               THEN  SERIALIZABLE             WHEN transaction isolation level   5               THEN  SNAPSHOT             ELSE NULL        END AS TRANSACTION ISOLATION LEVEL  FROM   sys dm exec sessions AS s        CROSS JOIN sys databases AS d WHERE  session id     SPID   AND  d database id   DB ID

User · Answer

just run DBCC useroptions and you ll get something like this   Set Option                  Value --------------------------- -------------- textsize                    2147483647 language                    us english dateformat                  mdy datefirst                   7 lock timeout                -1 quoted identifier           SET arithabort                  SET ansi null dflt on           SET ansi warnings               SET ansi padding                SET ansi nulls                  SET concat null yields null     SET isolation level             read committed

[sql] How to find current transaction level?

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008

Examples related to sql-server-2005

Examples related to isolation-level