xel file of the system_health session and gives information about the deadlock problems which were occurred. The system_health session collects various system data, and one of them is deadlock information. The system_health is the default extended event session of the SQL Server, and it started automatically when the database engine starts.

Transaction (Process ID XX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. As a first step, he decided to check the system_health session for the deadlocks. The error message obviously was indicating a deadlock problem. The database administrator decided to research the deadlocks problems because of the error message. Monitoring the deadlocks in SQL Server with system_health session We will also use the SQLQueryStress tool to generate a similar workload of the production system. The development team was using the following table to store the order numbers, and the following query was used to create the first row of the day. In the next sections, we will learn how database administrators analyze and resolve this deadlock problem. Under these circumstances, the team decides to receive consultancy service from an experienced database administrator. The development team realizes that it is a deadlock issue, but they could not find the main reason for the problem. In this real scenario, an in-house application returns an error to the users, and users notify the development team about this error. Now, let’s take a glance at the problem scenario. It chooses a victim from the transactions who are involved in the deadlock, forces it to rollback all actions.Īs can be seen from this explanation, deadlock in SQL Server is a special contention problem also, each deadlock has a unique characteristic, so the solution has different approaches according to problem characteristics. This contention issue is terminated by the SQL Server intervention. In this circumstance, database resources are affected negatively because both processes are constantly waiting for each other. A deadlock problem occurs when two (or more than two) operations already want to access resources locked by the other one. In general, we can find out various theoretical advice and examples about the deadlock problems on the web but, in this article, we will tackle a true deadlock story and learn the solution steps so that we’ll get a chance to work on a case based problem.įirst of all, let’s explain the deadlock concept. In this article, we will talk about the deadlocks in SQL Server, and then we will analyze a real deadlock scenario and discover the troubleshooting steps.

Sql balance lock