SQL Server Performance Forum – Threads Archive

dead loack

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.

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

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

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

thank you

That message refers to be someone used KILL command and recognise the hostname referred in the message. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

do you mean that someone manually killed the process?

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

thank you

And then findout who issued the KILL command that caught the attention. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

Hi! I run dbcc traceon(3605, 1205, -1) yesterday and this is what i found in log looks like spid 84 and 200 caused lock but haw do i find out waht db,procedure ,stetement was a problem? Starting deadlock search 4128 Target Resource Owner: 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 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 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 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 Deadlock cycle was encountered …. verifying cycle 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) 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) 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) End deadlock search 4128 … a deadlock was found.

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

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?

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?

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

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

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.

can i run dbcc traceon(3605, 1205, -1)
and dbcc traceon(1204) together to see more information
isn’t it too havy on server

What I meant was replace 1205 with 1204.

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

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

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…

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

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

You can run DBCC TRACESTATUS(-1) to know the current enabled trace status. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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. Guess BOL does not help you always <img src=’/community/emoticons/emotion-5.gif’ alt=’;-)’ />

But this DBCC TRACESTATUS information is available on BOL and specified too, its not undocumented. _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

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).

thank you traceon(-1) with traceon(1204) worked!

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?

Backup process never stops the trace, any insight from SQL error log? _________
Satya SKJ
Moderator
SQL-Server-Performance.Com

no error
prior to backup – trace messages
after nothing event log clear ,sql log clear

]]>