Showing posts from March 24, 2013

Detecting Transaction Isolation Level through Profiler Trace and other means on MS SQL Server 2005

I doubt that this comes up often, but I find myself in a bit of an argument about what isolation level will be used for transactions in our SQL Server database when the transaction is initiated by WebSphere.  There are various documents that indicate how WebSphere can over ride this value.

I decided to dig into this a bit further.

TRACE: In doing so, I found that I could monitor our SQL Server DB to determine this.

I have only found two places where the transaction isolation level is appearing:
1 - Sessions:Existing Connection.Text Data
2 - In a deadlock trace as part of the XML output.

If anyone can find it elsewhere, I'd love to hear from you.  However, this makes sense, since this is more-or-less controlled at the session level.

If found that 
DBCC USEROPTIONS would give me the *default* TIL for the db, but that if that TIL was altered by the session, it would not reflect that.

To get the TIL for *your session*, you can run the following query:

SELECTCASE transaction_isol…