How to record queries which create deadlock in SQL Server 2008 r2?

Question

Hi,

I am using SQL Server 2008 r2.

I am having Deadlock problem when process usually executes a stored procedures. i want to know which specific queries are creating deadlock.

Could you please inform me any script or an easy way through which I can do it?

Thanks!

Answer 1

These articles could help -
http://www.simple-talk.com/sql/learn-sql-server/how-to-track-down-deadlocks-using-sql-server-2005-profiler/
http://msdn.microsoft.com/en-us/library/ms178104(SQL.105).aspx

Another one with a trace flag option. Normally such flags are not kept on for an extended on a server. Just long enough to gather the required info and then turned off.
http://www.sqlservercentral.com/articles/deadlocks/74829/

---

Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers.
Thanks!
Aalam | Blog (http://aalamrangi.wordpress.com)

Answer 2

SQL Server Profiler has a Deadlock event.  Trace while the ddealock is occuring and you can view the statements involved.

---

Jack Vamvas sqlserver-dba.com