End users generally face a situation where the Sql Server application gets hangs. What users generally do is restart the machine for fixing the problem and then continue with their work. This restarting activity works for two to three weeks and after that, a point comes where the system gets crashed. Therefore, it is mandatory to learn causes of such hanging problem and measures to fix them before the machine gets crashed.

Well, there are three major reasons behind occurrence of the hanging situation and they are highlighted below:

• Problem with the server database file
• A deadlock situation had occurred
• Occurrence of the run-away thread

In this blog, we are going to focus upon Deadlock situation and measures to trace it in the SQL server.

What is Deadlock Condition In SQL Server ?

A situation in which two or more processes are waiting for the same resource and each process is looking for another one to complete before proceeding further is termed as deadlock. Whenever such situation arises and no technique is available to solve this conflict then, SQL server will automatically be choosing any one process as victim of deadlock state and going rollback it for other processes to continue. The SQL server is smart enough for identifying problem and ends deadlock in its own manner. The aborted transaction will be rolled back and an error will be sent to the application.

Now a question arises that how to counter SQL server deadlock performance? To get answer to this, we are going to learn capturing of deadlock information and few steps for resolving it.

How to Trace Deadlock in SQL Server?

A user can perform capturing of deadlock either in the SQL server error log or with help of Profiler or server side trace.

Choice #1: If you are choosing the SQL server error log then, you will have to enable one or both following trace flags:

• 1204: For providing information about nodes involved in the deadlock
• 1222: For returning deadlock details in an XML file format

You can either enable them separately or together by executing following commands in the SQL server query window:

Choice #2: If you have chosen SQL Server Profiler then, there is no need of any trace flags to be enabled. Basically, there are following three events occurring in deadlock situation and each of them comes in an event class, named as Locks:

• Deadlock Graph: This event provides an XML description of the deadlock and occurs simultaneously with Lock:Deadlock event class.
• Lock: Deadlock: The Lock:Deadlock event denotes two concurrent transactions, which are causing deadlock state.
• Lock: Deadlock Chain: This is produced to determine the process chain, which is causing deadlock in the server.

Determining Processes Involved in Deadlock

To determine processes or objects execute following command, by substituting object_id for the respective partition_id in it.

Generate XML File to Save Deadlock Graph Data

It is known that deadlock graph data is archived in XML file format therefore, you can create and save events separately on the machine. While configuring Trace Properties, click on Events Extraction Settings tab and check the checkbox of Save Deadlock XML events separately option.

Note: An XML deadlock graph is going to have two sections and they are described below:

Processes: This section comprises of the details of entire processes that were involved in the deadlock state. In addition, what they were currently running, level of isolation, and many other things are described related to the process.

Resources: It is the list of all the resources, which were involved in deadlock and locks each process acquired and interrupts the process request.

How to Prevent Deadlock In SQL Server?

We know that the SQL server is capable of curing itself in deadlock state but, this doesn’t mean that database administrator will ignore such situations. It is so because the deadlock victim might be an important business operation, which may cause considerable disruption on its failure. Database admin should be known from the fact that when deadlock happens with occurrence of 1205 error, the deadlock graph will detail the processes involved in the deadlock and then automatically set to its consistent state for eliminating such situation.

Conclusion

This article might serve itself as good piece of reference for dealing with deadlock in the SQL server. One need not to get hyper when such situation occurs because SQL server itself is quite enough in curing the problem by eliminating one process. Keep in mind that the aborted process may be an important one therefore, be aware from the process that is aborted by the SQL server.