trace is not capturing DeadLock

Question

trace is not capturing DeadLock

thbaig

Hall of Fame

Points: 3003
More actions
August 23, 2016 at 10:24 am

#312764

HI,
I found deadlock on one of my db server. at first I tried to capture it with profiler.I had added
Deadlock, Deadlock chain and deadlock graph, sql stmt start,sqlstmt completed as selection of events. Profiler only captured deadlock chain "Parallel query causing deadlock", but no graph or statement that is victim of deadlock.
So I enabled server trace with following sql
DBCC TRACEON(-1, 1204, 1222)
I have no deadlock event under SqlServer- Error Logs in SSMS.
Am i looking in correct place to see logs? or do I need to add some additional flags in trace ?
Thanks

Viewing 6 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: 1004504 More actions · Answer 1

Could be the deadlock hasn't reoccured.

You don'yt need 1204, that the SQL 2000 traceflag for capturing deadlocks, and to be honest you don't need 1222 in SQL 2012, use the system health extended event session, which captures deadlocks.

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

thbaig Hall of Fame Points: 3003 More actions · Answer 2

Thank you Gail for updated information.

I am studying these event and will get back if any other issue arises.

But just for knowledge, what could be the reason with the approach I followed ?

rajeshsbhat Grasshopper Points: 11 More actions · Answer 3

thbaig - Tuesday, August 23, 2016 10:56 AM
Thank you Gail for updated information.I am studying these event and will get back if any other issue arises.But just for knowledge, what could be the reason with the approach I followed ?

Even am facing the same issue as explained here. I have enabled trace flags 1222 and 1204 globally on a DB server (SQL 2012) to capture deadlock details. When I simulated the deadlock by running the queries it captured the details. But when the deadlock is happening thru' web service calls it is not capturing the error details at all. What could be wrong? We are seeing deadlock error for other DBs in the same errorlog file.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 4

rajeshsbhat - Thursday, January 19, 2017 4:06 AM
thbaig - Tuesday, August 23, 2016 10:56 AM
Thank you Gail for updated information.I am studying these event and will get back if any other issue arises.But just for knowledge, what could be the reason with the approach I followed ?
Even am facing the same issue as explained here. I have enabled trace flags 1222 and 1204 globally on a DB server (SQL 2012) to capture deadlock details. When I simulated the deadlock by running the queries it captured the details. But when the deadlock is happening thru' web service calls it is not capturing the error details at all. What could be wrong? We are seeing deadlock error for other DBs in the same errorlog file.

Check the system health extended event session as Gail has suggested. Here's an article on it. Extended events, especially in SQL Server 2012 and greater, are much more efficient than trace. They're likely to be more accurate too.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

thbaig - Tuesday, August 23, 2016 10:24 AM
HI,I found deadlock on one of my db server. at first I tried to capture it with profiler.I had addedDeadlock, Deadlock chain and deadlock graph, sql stmt start,sqlstmt completed as selection of events. Profiler only captured deadlock chain "Parallel query causing deadlock", but no graph or statement that is victim of deadlock.So I enabled server trace with following sql DBCC TRACEON(-1, 1204, 1222)I have no deadlock event under SqlServer- Error Logs in SSMS.Am i looking in correct place to see logs? or do I need to add some additional flags in trace ?Thanks

Your trace command above is incorrect. The "-1" needs to be the last operand.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)