I would like to show you how to register each deadlock occurrence to view the details later. By default, SQL Server solves deadlock situations but it does not make a big noise about it so you may not even know whether deadlocks occur in your database or not.

You don't necessarily need to know. It depends on the architecture and consequences that deadlocks cause in your system, but I actually find that possibility very useful. For example, I want to monitor deadlocks in my development and test environment to decide whether a new version of my system behaves differently than the current one or not. If a number of deadlocks grows or they starts to appear in places where they should not, it may be an indicator for a fix.

Deadlocks

Almost everybody can give an example of a deadlock and can reproduce it in a database. It is one of the skills that come from IT studies. SQL Server is especially easy to cause a deadlock situation because of its locking nature. In my career, most deadlocks I have seen in SQL Server have been between a sequence of inserts and updates within an explicit transaction on the one side and a big select query on the other. It is a very SQL Server specific.

No matter whether it is SQL Server or Oracle - deadlocks happen. They may be more frequent if architecture is bad or very rare if there are not many explicit transactions. Even if an architecture is good, totally eliminating deadlocks may be impossible just because of a system purpose or a way how it is used. Sometimes it is even not worth investing in it if consequences are not painful and they occur only a few times a year.

By writing this I wanted to make a key statement - "a deadlock is not an evil". Deadlocks are properly solved automatically by a database engine. One of the participant processes is chosen to be a victim and is killed. Database does not crash because of that. If an application has a retry logic (which is recommended for many reasons), it is not a big deal.

Logging

But even understanding that "a deadlock is not an evil" and "killing a victim is not a tragedy" you may want to know what deadlocks happened, when and all useful details. By default, SQL Server does not register much information about deadlocks. There is a trace flag 1222. If you set it on, deadlock information will be logged in SQL Server Error Log.

The following statement enables 1222 flag globally for the whole instance - if a deadlock occurs in ANY of the databases on an instance, it will be registered.

dbcc traceon (1222, -1)

The following statement displays a list of enabled trace flags:

dbcc tracestatus

Now, if a deadlock occurs, details will show up in Error Log like below:

It provides enough information to see what happened, which processes were involved etc.

With trace flag 1222 enabled, you can set alerting based on Error Log and you will get notifications when the event occurs.

Warning

One important comment at the end - enabling ANY trace flag may affect performance of your system as SQL Server needs to execute additional work. It is strongly adviced to enable them wisely with appropriate testing before production.