title: How do I find the source of a SqlException (1205) Deadlock?
body: <p>I'm dealing with a fairly large-scale C# application which occasionally hits a SQL Server deadlock. I can't figure out what's causing it. My bandaid solution for now is:</p>
  
  <p>(1) Catch the SqlException.</p>
  
  <p>(2) See if the error code is 1205 (i.e. deadlock).</p>
  
  <p>(3) If it is, Sleep() for a couple of seconds and retry the entire transaction. You can assume the previously failed transaction was successfully rolled back.</p>
  
  <p>This works. The problem occurs only once or twice per week so the performance penalty is trivial. It still bugs me that it's occurring though. How do I figure out <em>why</em> it is occurring?</p>
  
  <p>I know a deadlock occurs when two or more SQL Server threads are contending for the same resources. I obviously know which one of my transactions LOSES that battle. It's always the same query. But I'd like to know which which transaction is WINNING the battle. Maybe it's the same code block. Maybe not. I have no way to tell. Is there some special tool I should use to find the deadlock's source?</p>
  
  <p>More info: The losing transaction isn't doing anything particularly exotic; just two large deletes via ExecuteNonQuery() followed by two large bulk inserts using the SqlBulkCopy class -- all in the same SqlTransaction. Both READER_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are turned on. There are no humans making ad-hoc queries against the database. My application is the only user.</p>
  
  <p>Again, this works flawlessly 99.99%+ of the time... the deadlock is quite rare. It manifests  only once or twice per week.</p>