.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

How to capture isolation level changes in Profiler

Posted By:      Posted Date: October 13, 2010    Points: 0   Category :Sql Server

I'm looking for a way to catch when application code changes the isolation level for a connection.

When code such as this is run: 

tran = con.BeginTransaction(IsolationLevel.Serializable);

sys.dm_exec_sessions shows the change in isolation level from immediately before to immediately after the statement, but Profiler only shows the transaction starting, not the change of isolation level.  

TM: Begin Tran starting
TM: Begin Tran completed

Any subsequent Audit Login event in Profiler will report that the isolation level has changed, but this is due to the bug (by design) with the connection pool not resetting the isolation level when the connection is recycled.

The environment is SQL 2008 sp1.



View Complete Post

More Related Resource Links

What is the default isolation level (SQL Server 2008)

Hi, My database settings are: SET ALLOW_SNAPSHOT_ISOLATION ON READ_COMMITTED_SNAPSHOT ON Does this mean I don't have to explicitly have: Set Transaction Isolation Level Read Committed for each proc, i.e. because I have the two settings at the database level there is no need set the Transaction Isolation Level in each proc or does this need to be done as well as the database level settings. Cheer, Peter  

multiple executions of MERGE statement: Help with suitable TRANSACTION ISOLATION LEVEL

Folks, I am reasonably new to SQL Server. I am using SQL Server 2008 (no SP) on Windows XP. I am using the MERGE statement within a TSQL procedure to update a master/detail table pair (Master/Child), in which the MERGE inserts into the MASTER if a record based on the primary key doesn't exist and does, effectively nothing, if it does (well, it does an UPDATE set PK=PK so the record is passed on to the OUTPUT statement for insertion into the CHILD). Regardless as to the situation, the CHILD record has a record created when the MASTER exists or doesn't exist. Now, this code works fine with the standard TRANSACTION LEVELS. But I don't know what to do when I am running two instances of the same MERGE statement at the same time. One execution could create a record in the MASTER which the other process might try and create 5 minutes later. I really don't know what SET TRANSACTION ISOLATION LEVEL to use to allow both processes to run at the same time. I have looked at: ALTER DATABASE $(usedbname) SET READ_COMMITTED_SNAPSHOT ON; ALTER DATABASE $(usedbname) SET ALLOW_SNAPSHOT_ISOLATION ON; But these don't seem to work with their associated TSQL calls. I know this is a complex issue, but as a new SQL Server user, I didn't know where else to go. regards Simon                              

Isolation level in ADO.net

I am trying to apply transaction during updating in my Financial table "F_Ledger". I want that when Transaction A is updating the record in this table Transaction B should be able to read the records but could not update it till Transaction A is committed. For this i wrote following code:         Public Sub BeginTransaction()            oTransaction = oSQLConnection.BeginTransaction(IsolationLevel.RepeatableRead)        End Sub  here  oTransactionis an instance of "SqlTransaction" class and "oSQLConnection" is an instance of "SqlConnection" objectAfter this i called the BeginTransaction and did not let the transaction to commit by applying breakpoint.Now when i try to execute "select * from F_Ledger"  in SQL server Query analyser it hangs...which implies that since first transaction ("suppose A") has not completed (i.e Committed)  therefore "select * from F_Ledger" ("suppose B") is not executing. I tried with all the isolation levels but this happens in all cases. So i am wondering why "select * from F_Ledger", which is a read operation not working with RepeatableRead isolation type, whick locks the resource in shared mode. Same is also

Which profiler events I need to capture to find out timing out queries?



I understand how can I capture long running quieries based on duration. But I'm trying to figure out how I can do it with quiry which are timing out? For example if ASP.NET executes a query against SQL server and it did not completely within say 60s, will that even be written in profiler as RCP:Completed or it would not be written at all since ASP.NET will kill connection at that point and return error to user?





Exception Handling: StackFrame Capture not capturing last level method call in release mode


I am trying print the exception details frame by frame along with other debug info (code below). In the code as you can see the DummyCall3 throws and ApplicationException and the expectation is that the stack trace will have information regarding DummyCall3. Below are the behavior I am getting with .NET 4.

  • Using the "Debug" build, when I run it, I get expected results (DummyCall3 > DummyCall2 > DummyCall > RunTest)
  • Using the "Release" build however, the DummyCall3 method is somehow missing from the stack trace. Now I only get (DummyCall2 > DummyCall > RunTest)
  • I add a dummy statement in the DummCall3 method before I throw the exception, and rerun using "Release" mode and now it works as expected with strack trace info (DummyCall3 > DummyCall2 > DummyCall > RunTest)
  • If I switch the framework version to .NET 3.5 it works as expected in both Release and Debug modes?

I would like to know if this behavior is by design or a bug? if it is by design, what was the reasoning behind it. Appreciate any input on this.


  class StackFrameTest
    public void RunTest()

Isolation Level set to Read Committed Snapshot, Deadlock Graph shows Read Committed


I must misunderstand something here. I have altered the DB to allow READ_COMMITTED_SNAPSHOT and some deadlock situations have been solved. Nevertheless, in a very similar situation, I'm getting deadlocks and the deadlock graph (XML) shows me the following:

isolationlevel="read committed (2)"

What do I miss here?



If I place an event handler on the package level will it capture all events?

If I place an event handler on the package level will it capture all events?
Mr Shaw

Isolation Level Read Uncommitted , Nolock & Replication



    Scenario : 

1. We have replication for Database D1.T1, D1.T2 , D1.T3
        Also have replication for Database D2.T1, D2.T2, D1.T3
2. We have view setup like
   Create View V1
   Select * from D1.T1
   Union All
   Select * from D2.T1

   Similarly V2 & V3
3.  We have Procedure using view V1, V2, V3

Fact. Set ISOLATION LEVEL READ UNCOMMITTED is always use inside the procedure

Question :

    1.  Procedure using Views V1 without NOLOCK on it (but have ISOLATION LEVEL UNCOMMITTED) , will that impact the replication on table T1, T2, T3  or Performance of the procedure or any Blocking?

   2. Procedure using Views V1 with Nolock on it (also have Isolation level Uncommitted) , Will that impact the replication on table T1, T2, T3 or performance of the procedure or any blocking?

3. Procedure using Views V1 with Nolock on it (Also have isolation level uncommitted) and
(Change my view as
  Create View V1
   Select * from D1.T1 With (nolock)
   Union All
   Select * from D2.T1 with (nolock)<

Can I configure the default isolation level for all my SSRS reports.


By default connections to SQL Server are made Read Committed. However in a lot of situation this can result on locking of other users when a big SSRS report is executing. Is there a way to configure my SSRS server that it will connect by default with isolation level Read Uncommited to the database.

At this moment you need to specify the transaction isolation level in every dataset of the report. This will result in problems of somebody forget to specify. By configuring this on server or connection level, you know for sure that all reports are executing with Read Uncommitted.

More information about this topic canbe found on my blog: http://www.keepitsimpleandfast.com/2010/11/bad-performance-and-lockings-occur-ad.html



Isolation Level


dbcc useroptions gives me what isolation level is set on the system. How do i find if it is pessimistic or optimistic?

In which situations do we change the server wide isolation levels? eg in OLTP vs Reporting vs DSS

What have you observed in your environments?

A Low Level Look at ASP.NET Architecture

ASP.NET is a powerful platform for building Web applications, that provides a tremendous amount of flexibility and power for building just about any kind of Web application. Most people are familiar only with the high level frameworks like WebForms and WebServices which sit at the very top level of the ASP.NET hierarchy. In this article I'll describe the lower level aspects of ASP.NET and explain how requests move from Web Server to the ASP.NET runtime and then through the ASP.NET HTTP pipeline to process requests.

Need help using control adapters at the machine level

Hi guys!

Let me explain our situation. I do web development at a university at which we have to meet strict accessibility guidelines. I've specially modified the adapters (as well as extended CompositeDataBoundControlAdapter to include GridView) for use on our site and to use with our global stylesheets as well as compiled the control adapters into a DLL.

The control adapters now all reside in the WebServices.CssFriendlyAdapters DLL.  (For example, WebServices.CssFriendlyAdapters.GridViewAdapter).

Currently, when someone drops an App_Browsers folder in to their site with the adapters specified, the adapters work. However, we'd like to be able to specify this in the C:\WINDOWS\Microsoft.NET\Framework\(version number)\CONFIG\Browsers directory so that the adapters work automatically without anyone having to move anything into App_Browsers for every site. To ensure that someone can still use the "old way" or will not be confused by their controls being adapted, I've written in code that uses the base rendering methods unless xhtmlConformance is set to "Strict" in the site's web.config file.

Anyway, on to the problem. When trying to compile the controlAdapters into the C:\WINDOWS\Microsoft.NET\Framework\(version number)\CONFIG\Browsers directory using aspnet_regbrowsers.exe (see http://msdn2.microsoft.com/en-us/library/ms229858.a

Wicked Code: Taking Silverlight Deep Zoom To The Next Level


With the help of Silverlight Deep Zoom and a remarkable control named MultiScaleImage, you can create scenes with many levels of zoom. Jeff Prosise illustrates with what else but the Mandlebrot set.

Jeff Prosise

MSDN Magazine July 2009

Form Filler: Build Workflows to Capture Data and Create Documents


Learn how to create a workflow that uses InfoPath forms and other office documents for passing data to targeted activities and for use in Office documents.

Rick Spiewak

MSDN Magazine June 2008

Performance: Find Application Bottlenecks with Visual Studio Profiler


We will introduce you to the Visual Studio Profiler by walking through a sample performance investigation, pinpointing code inefficiencies in some sample applications.

Hari Pulapaka and Boris Vidolov

MSDN Magazine March 2008

.NET Profiling: Write Profilers With Ease Using High-Level Wrapper Classes


Here Joachim H. Fröhlich and Reinhard Wolfinger show you how to get all the great functionality of the .NET Profiling API the easy way, with custom wrappers.

Joachim H. Fröhlich and Reinhard Wolfinger

MSDN Magazine April 2006

Test Run: Low-Level Web App UI Test Automation


As Web applications have become more complex, testing them has become more important. There are many testing techniques available to you. For example, in the April 2005 issue of MSDN®Magazine, I describe a simple JScript®-based system that can test a Web app through its UI by using the Internet Explorer Document Object Model.

James McCaffrey

MSDN Magazine October 2005

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend