Results 1 to 14 of 14

Thread: Set trace flags at the command prompt(Urgent)

  1. #1
    Join Date
    Nov 2002
    Posts
    36

    Set trace flags at the command prompt(Urgent)

    Hello,

    I would like to get the complete information of deadlocks, Can anyone guide me how do i run trace flags (-1, 1204, 3605) from command prompt.

    Thanks
    Sejal

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Use OSQL.EXE to run dbcc

    osql.exe /e /Syourserver /Q"dbcc traceon (-1,1204)"

  3. #3
    Join Date
    Nov 2002
    Posts
    36

    sql

    Is there a way to start server using sqlservr with a trace flag (-1, 1204, 3605) I tried but it hangs, any help is appreciated.

  4. #4
    Join Date
    Nov 2002
    Posts
    36

    sql

    Is there a way to start server using sqlservr with a trace flag (-1, 1204, 3605) I tried but it hangs, any help is appreciated.

  5. #5
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Change the starup parameters in Enterprise Manager, server properties. You have to use -T1204, you don't need -1.

  6. #6
    Join Date
    Nov 2002
    Posts
    36
    Thanks A Lot.
    I'm having lot of deadlocking occurring in my server, when I do DBCC Traceon(-1,1204,3605). I don't get the commands which cause deadlock, instead I get
    (Transaction (Process ID 51) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..) Would this startup parameter gives me detailed information or Is there a way I can capture detailed information. I would appreciate your help

  7. #7
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Start sql server with 3605,1204,1205 trace flags.

  8. #8
    Join Date
    Nov 2002
    Posts
    36
    Should I startserver with setting the Startup Parameters -T1204,3605,1205? Please guide me, I'm running MS SQL Server 2000 with SP2 and has clustering enviornment.

    Thanks
    Sejal

  9. #9
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    It is same whether you set the traceflag using dbcc -1,xx or use startup parameter.

    Flag 3605 logs all the details in sql server error log. Did you check error log?

  10. #10
    Join Date
    Nov 2002
    Posts
    36
    I didn't see any info as there is no deadlock, yesterday I restarted the server with -T1204 as startup parameter, before I had rum DBCC traceflagon(-1, 1204,3605) but couldn't get full info in sql log only that the deadlock has occurred, I want the command thats causing deadlocks.

    Should I change my startup parameters to -T(1204,3605)?

    Thanks A lot

  11. #11
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    When you run

    dbcc tracestatus(-1)

    do you see all trace flags you set to ON.

    Change startup to

    -T1204
    -T1205
    -T3605

  12. #12
    Join Date
    Nov 2002
    Posts
    36
    I still don't see the commands and full description, Am I missing something

    The sql errorlog reads
    Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction..

    Thanks In Advance

    Sejal

  13. #13
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Try to create deadlock using two connections in query analyzer, in one run

    begin tran
    update titles set title=''
    waitfor delay '00:00:05'
    update authors set address='a'

    and in the second one run

    begin tran
    update authors set address = ''

    update titles set title=''

    One of them will deadlock, make sure you rollback the other one.

    I just tried it on my server with trace flags 1204, 1205 and 3605 turned on using

    dbcc traceon(-1,1204)
    dbcc traceon(-1,3605)
    dbcc traceon(-1,1205)

  14. #14
    Join Date
    Nov 2002
    Posts
    36
    Thanks A lot.

    -Sejal

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •