Post reply

How can I tell what transaction level SQL statements are running under with Profiler and dead lock trace with SQL Server 2000

  • January 9, 2008 at 1:49 pm

    #202210

    Hi

    please excuse me if I have posted in the wrong forum...

    I am trying to find out if the dead locks that I am experiencing under SQL Server 2000 will dissappear if we upgrade to SQL Server 2005 and enable the new isolation level 'read commited using row versioning'. In order for the new feature to help us I need to establish if the current dead locks that we are experiencing are occuring with statements that are running under the read commited isolation level. Ideally I would look in the code but unfortunately I do not have that option 🙁

    In SQL Server 2005, if you enable the dead lock trace flag 1222, the trace will tell you the isolation level that the statements were running under.

    1. Is there a way of getting this information from the SQL Server 2000 dead lock trace (1204)?

    2. Is it possible to configure SQL profiler to report what isolation level the individual profiled statements are running under? I have tried and failed.

    Thanks for your time

    Adam

  • January 9, 2008 at 10:21 pm

    #766472

    SQL Server 6.5 was having pagelevel locking but SQL Server 7.0 onwards sql server is having rowlevel locking. This locking is dynamic and depend on Isolation level you are using for sql server.

    Now to resolve the deadlock problem you can use following two trace flag

    1204

    3605

    for more detail pl. refer the following link

    http://www.mssqlcity.com/Articles/General/SQL2000TF.htm

    forums.databasejournal.com/archive/index.php/t-33272.html

    Good Luck!

    ---------------------------------------------------
    "Thare are only 10 types of people in the world:
    Those who understand binary, and those who don't."

  • January 10, 2008 at 12:56 pm

    #766818

    thanks for the response, unfortunately I don't think I have made myself clear in my orginal post.

    I understand locking and how to generate the dead lock trace output in SQL Server 2000. What I was wanting to know is, is there a way to tell what transaction isolation level the statements (that were involved in the deadlock) were executing under - either via profiler or the 1024 trace output.

    In SQL Server 2005 the 1222 dead lock trace output has this information but I'm not sure how to get the same from SQL Server 2000.

    Thanks

    Adam

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply