Find Isolation Level of transaction and Database | SansSQL

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.

Isolation Level
  • Read uncommitted
  • Read committed
  • Repeatable read
  • Serializable
  • Read committed snapshot
  • Snapshot
SET TRANSACTION ISOLATION LEVEL <<Isolation Level>>

Find Isolation level of a Database
USE <<Database Name>>
GO
DECLARE @UserOptions TABLE ([Set Option] NVARCHAR(50), [Value] NVARCHAR(50))
INSERT INTO @UserOptions Execute ('DBCC USEROPTIONS')
SELECT * FROM @UserOptions WHERE [Set Option] = 'isolation level'

You can download the script from here.

3 comments:

Unknown said...

Wonderful ! Thanks for Sharing this article keep update this kind of nice articles ..

George Bondurant said...

I have seen and read out all pages as well as all articles and I think your post is exceptionally fascinating and generally, I continue searching for like this sort of sites where I learn or get new idea. so I need to sugguest you one thought you will review the task custom essay writing service and furthermore review the rule of remark. I have to thank you for your minute because of this inconceivable read! A good blog always comes-up with new and energizing data and keeping in mind that understanding I have feel that this blog is truly have all those quality that qualify a blog to be a good one.
Custom essay writing service

Monnika Jacob said...

Understanding isolation levels are not much easier, it examines every transaction that takes place from one server to another. I am sure, your post will help us in tracking isolation level of transactions. Thanks for sharing this. Coursework Writing Services

Post a Comment

Ads