March 28, 2013 at 9:50 am
I was asked on an interview how to find deadlock information without using SQL Profiler or running a trace. They didn't give me an answer, but it sounded like they were suggesting you could pull that information with one of the dynamic management views. This is new to me. I know how to find transaction information and blocking information, but not the results of a deadlock. Did I miss the memo on this?
March 28, 2013 at 11:06 am
Gabriel P (3/28/2013)
I was asked on an interview how to find deadlock information without using SQL Profiler or running a trace. They didn't give me an answer, but it sounded like they were suggesting you could pull that information with one of the dynamic management views. This is new to me. I know how to find transaction information and blocking information, but not the results of a deadlock. Did I miss the memo on this?
There are several ways I remember. There are trace flags that you can enable to get deadlock events into the event log (1222 and 1204) . There is also the sys.dm_tran_locks DMV and you can also use extended events to name but a few.
SQL DBA
Every day is a school day, and don't trust anyone who tells you any different.
http://sqlblogness.blogspot.co.uk
March 28, 2013 at 11:10 am
The traceflag 1222 (don't use 1204, it's SQL 2000 and before). Extended events system health session too it runs automatically so all you need to do there is query it.
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
March 28, 2013 at 11:11 am
Ness (3/28/2013)
There is also the sys.dm_tran_locks DMV
That doesn't show deadlocks. It shows granted and waiting locks. There's no DMV that shows deadlock info
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
March 29, 2013 at 6:53 am
I so wish we had voting here. i just want to vote up Gail's answer & move on. Trace flags and and the system_health Extended Event session would be my two answers 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
March 29, 2013 at 9:54 am
Thanks Gail. One of the things in 2008 that flew under my radar.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply