deadlock error msg 1205

Question

deadlock error msg 1205

SQL_Hunt

SSC-Dedicated

Points: 33518
More actions
June 16, 2015 at 12:55 pm

#305130

Hi Experts,
To get deadlock victim alert do we need to turn on deadlock trace flags or if I create an alert and if there's any deadlock incident happen, it will throw alert (if no deadlock flag is set)?
Please suggest
Thanks.

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

Traceflag 1222 is to get the deadlock graph written to the error log.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 2

I simulated a deadlock situation. I found the below error in the other screen as SQL chosen tjhat query as victim. I did not enable any trace flag.

Msg 1205, Level 13, State 51, Procedure sp_simulatedeadlock, Line 64

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

Thanks.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

As I said, Traceflag 1222 is to get the deadlock graph written to the error log.

The error's thrown no matter what, which means that any alert on the error will fire no matter what. The traceflags are to get the deadlock graph written out.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

SQL_Hunt SSC-Dedicated Points: 33518 More actions · Answer 4

O yes, that's true. I did not recall to enable trace flag as foglight does that stuff and for that no need to enable the trace flag. Was trying to see if any such situation ever arises (deadlock victim) and if I setup an alert 1205 error will SQL send that (if I dont enable trace flags)

Thanks your answer indeed.

Thanks.