Results 1 to 2 of 2

Thread: Trace SQL text

  1. #1
    Join Date
    Jan 2004
    Posts
    7

    Unanswered: Trace SQL text

    When deadlocks occur, the SQL text involved will be saved in server log.
    However, sometimes the SQL text is truncated or query is not being logged. Is there anyway to do a full logging? Any trace flags to turn on?

  2. #2
    Join Date
    Sep 2003
    Location
    Switzerland
    Posts
    443
    1> dbcc traceon(1204)
    2> go
    DBCC execution completed. If DBCC printed error messages, contact a user with
    System Administrator (SA) role.

    Simulate a deadlok in two user tasks
    In user task 1


    1> begin tran
    2> go
    1> delete foo_1
    2> go

    In user task 7

    1> begin tran
    2> go
    1> delete foo_2
    2> go
    1> select * from foo_1
    2> go

    In user task 1

    1> select * from foo_2
    2> go

    00:96/04/03 08:10:16.27 server Deadlock Id 1 detected
    Deadlock Id 1 detected. 1 deadlock chain(s) involved.

    Process 7 was executing a SELECT command at line 1.
    Process 1 was executing a SELECT command at line 2.
    Process 1 was waiting for a 'shared intent' lock on the 'foo_2' table in
    database 5 but process 7 already held a 'exclusive table' lock on it.
    Process 7 was waiting for a 'shared intent' lock on the 'foo_1' table in
    database 5 but process 1 already held a 'exclusive table' lock on it.

    Process 7 was chosen as the victim. End of deadlock information.

Posting Permissions

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