SQL Server Performance

dead loack

Discussion in 'General DBA Questions' started by NewDBA, Sep 29, 2003.

  1. NewDBA Member

    Is that possible to identify dead lock after it's gone?
    If not is that possible to setup some allert to go to sql log saing what causing dead loack.
    i don't want to run sql profiler or execute sp_block... stored procedure every sec.
    because server is loaded very havily.
  2. Twan New Member

    Hi there,

    Check out "troubleshooting SQL Server, deadlocks" in Books online. There is a trace flag (1204) that can be set on the server to record the deadlock chain into the error log

    You can also use Profiler to trap dealock errors and deadlock chains

    Cheers
    Twan
  3. NewDBA Member

    Is that alert will eat SQl resources.
    Can i leave it for a couple of days?

    i got this message sql log:
    process id 1 was killed by hostname...

    is that mean this is exact time when deadlock happened.
    thank you
  4. satya Moderator

    As referred in your case using Trace flag is ideal as opposed to use PROFILER due to sheer traffic on database. Also just for information refer to this linkhttp://www.sql-server-performance.com/deadlocks.asp about deadlocks.

    HTH


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  5. NewDBA Member

    thank you
  6. satya Moderator

    That message refers to be someone used KILL command and recognise the hostname referred in the message.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  7. NewDBA Member

    do you mean that someone manually killed the process?
  8. bambola New Member

    To add to what's been said, you can use dbcc traceon(3650, 1205, -1). 1205 will give you more detailed information, 3605 write it to error log and -1 means at server level. so all deadlocks will be written to log.

    Bambola


  9. NewDBA Member

    thank you
  10. satya Moderator

    And then findout who issued the KILL command that caught the attention.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  11. NewDBA Member

    Hi!<br />I run dbcc traceon(3605, 1205, -1) yesterday and this is what i found in log<br />looks like spid 84 and 200 caused lock but haw do i find out waht db,procedure ,stetement was a problem?<br /><br /><br /><br />Starting deadlock search 4128<br /><br />Target Resource Owner:<br />ResType:LockOwner Stype:'OR' Mode: U SPID:200 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x2745764<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x3c520800<br />Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:200 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x2745764<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x3c520800<br />Node:2 ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x220e964<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x1fc6b460<br />Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:200 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x2745764<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x3c520800<br /><br />Deadlock cycle was encountered .... verifying cycle<br /><br />Node:1 ResType:LockOwner Stype:'OR' Mode: U SPID:200 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x2745764<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x3c520800 Cost<img src='/community/emoticons/emotion-6.gif' alt=':(' />0/8B2A24)<br />Node:2 ResType:LockOwner Stype:'OR' Mode: IX SPID:84 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x220e964<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x1fc6b460 Cost<img src='/community/emoticons/emotion-6.gif' alt=':(' />0/139F4)<br />Cycle: ResType:LockOwner Stype:'OR' Mode: U SPID:200 ECID:0 Ec<img src='/community/emoticons/emotion-6.gif' alt=':(' />0x2745764<img src='/community/emoticons/emotion-11.gif' alt='8)' /> Value:0x3c520800 Cost<img src='/community/emoticons/emotion-6.gif' alt=':(' />0/8B2A24)<br />End deadlock search 4128 ... a deadlock was found.<br />
  12. satya Moderator

    You need to find which query and process is causing deadlock.

    For instance controlling code this way :
    begin tran
    dml
    end tran
    ... will let the transaction to complete in chuncks, and let SQL handle the locking while processing the queries.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  13. NewDBA Member

    i have ton's of dbs how would i know which one even start?
    Also i didn't write the code.
    i was hoping narow the search using this trace .
    So the message actualy useless?
  14. NewDBA Member

    Also i don't quite understand.
    Why the messages like
    dead lock was not found ....
    or dead lock was found .....

    apeared just eyesteaday right after i started the trace.
    and then i don't have any sign in sql log that trace is on.

    Are messages only go to log when there is dangerous situation?
  15. satya Moderator

    Only when you enable these trace you will be able to see the deadlock or other warning messages.
    As mentioned in the trace message refer to the current activity for SPID:200 and check the command used with SP_WHO & SP_WHO2.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  16. NewDBA Member

    Yes i know they only appear when trace is on.

    But it's on from yesterday and curently too.
    Althought it's on ,i don't have any messages in log anymore
  17. ykchakri New Member

    The messages will be recorded into log only when there is a deadlock situation. And I guess trace flag 1204 (instead of 1205) needs to be set to capture dead lock information.
  18. NewDBA Member

    can i run dbcc traceon(3605, 1205, -1)
    and dbcc traceon(1204)

    together to see more information
    isn't it too havy on server
  19. ykchakri New Member

    What I meant was replace 1205 with 1204.
  20. NewDBA Member

    I set up this trace
    i run this in master db
    dbcc traceon( 1205)
    dbcc traceon( 1204)

    then run this to make sure that trace set
    DBCC TRACESTATUS(-1)

    After that i was acurately checking error log for deadlock but found nothing

    So i 2 week after i run this again

    DBCC TRACESTATUS(-1)

    this is what i get as a result:

    Trace option(s) not enabled for this connection. Use 'DBCC TRACEON()'.
    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    So those trace just for current connection?
    Maybe i can set them somehow up using startup parameters?
    /Ttrace# 1204

    I actualy don't know the cintasix
  21. satya Moderator

    YEs BOL refers
    Trace flags are used to customize certain characteristics controlling how Microsoft® SQL Server™ operates. Trace flags remain enabled in the server until disabled by executing a DBCC TRACEOFF statement. New connections into the server do not see any trace flags until a DBCC TRACEON statement is issued. Then, the connection will see all trace flags currently enabled in the server, even those enabled by another connection.


    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  22. NewDBA Member

    i am really sorry but i don't understed this
    For me it sounds that i enable an other trace then i will see not only it but all another
    For expm. if two week ago i enabled 1204 and 1205 for some other connection then

    today when i run it for an other connection i will get message telling me that there is no trace,but after i start trace 1204 only i will also see my old 1205?

    this sound very strange...
  23. satya Moderator

    New connections into the server do not see any trace flags until a DBCC TRACEON statement is issued.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  24. NewDBA Member

    i try this on test server it really worked

    But for production i enabled an other trace and it show just it after
    checking status
  25. satya Moderator

    You can run DBCC TRACESTATUS(-1) to know the current enabled trace status.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  26. ykchakri New Member

    There is an undocumented flag '-1' that makes the trace flags apply to all connections. Run DBCC TRACEON(-1) from a connection and you will see your other trace flags 1205 and 1204 applied to all connections. <br />Guess BOL does not help you always <img src='/community/emoticons/emotion-5.gif' alt=';-)' />
  27. satya Moderator

    But this DBCC TRACESTATUS information is available on BOL and specified too, its not undocumented.

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  28. ykchakri New Member

    I'm talking about DBCC TRACEON(-1) not DBCC TRACESTATUS(-1). '-1' is not documented as a trace flag anywhere or atleast it is not documented to do the trick that I talked about (enabling trace flags for all connections).
  29. NewDBA Member

    thank you traceon(-1) with traceon(1204) worked!
  30. NewDBA Member

    Hate to come back to this but something is missing here.
    I run the trace on query analyzer in test server:

    dbcc traceon(3605, 1205, -1)

    Then closed query analyzer and open another one .
    Run dbcc tracestatus(-1).
    All traces was there.

    Next day i checked the sql log and notice then up to 3 am today the trace was running and checking for deadlocks.

    Then in 3:30 am dbs were backup and messeges from trace didn't apear anymore.

    I run dbcc tracestatus(-1) again :the message reported no traces.
    Then i run :
    dbcc traceon (3)

    and output returned 3605,1205,3

    The question:
    Is backup process stopped the trace?
    Or if there is some timeout / expiration for trace?

  31. satya Moderator

    Backup process never stops the trace, any insight from SQL error log?

    _________
    Satya SKJ
    Moderator
    SQL-Server-Performance.Com
  32. NewDBA Member

    no error
    prior to backup - trace messages
    after nothing

    event log clear ,sql log clear

Share This Page