August 23, 2016 at 10:24 am
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
August 23, 2016 at 10:32 am
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
August 23, 2016 at 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 ?
January 19, 2017 at 4:06 am
thbaig - Tuesday, August 23, 2016 10:56 AMThank 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.
January 19, 2017 at 9:20 am
rajeshsbhat - Thursday, January 19, 2017 4:06 AMthbaig - Tuesday, August 23, 2016 10:56 AMThank 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
January 19, 2017 at 10:44 am
thbaig - Tuesday, August 23, 2016 10:24 AMHI,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
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply