How to monitor deadlocks

You can use Event Notifications to get notified whenever a deadlock happens on the server:

See:

• Immediate deadlock notifications without changing existing code by Mladen Prajdić
• Using SQL Profiler to Resolve Deadlocks in SQL Server by Jonathan Kehayias

Additional links:

• The Anatomy of a Deadlock - Jonathan Kehayias
• Anatomy of a Deadlock - Part Deux - Jonathan Kehayias
• Read/Write deadlock - by Remus Rusanu

The first thing to do is look at the deadlock graph and see exactly what's going on. Then you can consider your options, which usually boil down to a) modify the application to standardize on an order of locking objects (e.g. if possible given the application logic, make a convention with your developers to always lock tables in alphabetical order) or b) see if you are really operating at the appropriate isolation level in each transaction.

I would go with automatic server side tracing of 2 main issues:

• long queries (you establish duration that's long for your environment)

• deadlocks - check Deadlock graph and Lock:Deadlock chain

You will get trace files every day and you can monitor the action - see if some scheduled event is causing issues. You can see more details about profiling production in this question: Using SQL Profiler on a database that's in production.