Data Management

Track down deadlock errors with the SQLDiag utility

SQLDiag is a diagnostic tool DBAs can use to capture server events for thorough analysis. In this article, Tim Chapman explains how using this tool can let you avoid some deadlock pitfalls.

SQLDiag, introduced in SQL Server 7.0, is a very handy SQL Server utility that you can use to monitor your database server for problems. It is a command-line tool that was created to provide necessary information for Microsoft customer support calls. When run, the tool outputs data from different SQL Server utilities into a text file for you to view. This text file can contain information about the configuration, the database, and the error log.

In SQL Server 2005, you can set up SQLDiag to run as a service, as well as use it as a command-line utility. However, for the basis of our discussion, I will assume that you are using a non-clustered SQL Server 2000 machine, which means I will issue our commands via the command prompt.

SQLDiag in action

I am always able to absorb technical information better by doing rather than by reading, so let's take a look at an example of how the SQLDiag tool can help our DBA lives. In this example, I will show how to capture the statements on a database server that is causing deadlocks in order to research and (with some skillful tuning) avoid them.

Weekly SQL tips in your inbox
TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system.
Automatically sign up today!

If you are unfamiliar with deadlocks, and how they can wreak havoc in your database system, refer to one of my previous articles, where I explain a little bit about deadlocks and how you can capture and retry them in SQL Server 2005.

Before I begin catching deadlock information on the database server, I must enable a couple of trace flags on the server. A trace flag is a type of status that can be set in SQL Server to illicit certain responses by the server. In this case, I want to enable trace flags that will keep watch for deadlock errors, and to write that information to the error log when they occur. To enable these trace flags, run the following commands: (you must be a member of the SQL Server system administrators' role to run the DBCC command)

DBCC TRACEON(1204, -1)
DBCC TRACEON(1205, -1)
DBCC TRACEON(3605, -1)

The 1204 flag returns the types of locks participating in the deadlock encountered. The 1205 flag returns more detailed information regarding the statements being run at the time of the deadlock. The 3605 flag sends the trace information to the error log, which the SQLDiag tool will later output to a text file. The "-1" parameter to the command tells SQL Server to apply the trace flag to all connections to the server.

Now that the system is ready for deadlock capture, it is time to create a deadlock situation. In Listing A, I create some tables to use to cause a deadlock.

Now that the tables are created, I'll insert one row in each table so that we have a value to update. I will use two adjacent running update statements to cause the deadlock. View Listing B.

This is the fun part. In the script above, I added one row to each table in order to run an update statement. The update statements that I use will contend with each other for the table resources and will eventually illicit a deadlock. I will need two separate database connections for this part. Cut and paste this SQL Server script into one connection. I will refer to this as connectionA. View Listing C. Next, cut and paste this SQL Server script  into a second database connection. I will refer to this as connectionB. View Listing D.

To create a deadlock, you want to run the script in connectionA, followed by immediately running the script in connectionB. As indicated by the WAITFOR DELAY statement in connectionA, you will have approximately 3 seconds to switch connections and execute the script in connectionB. If it executes correctly, you will receive an error very similar to the one below in one of the query windows:

Server: Msg 1205, Level 13, State 50, Line 1
Transaction (Process ID 53) was deadlocked on {lock}
resources with another process
and has been chosen as the deadlock victim. Rerun the transaction.

The deadlock happens because there is a contention for resources between the two connections. ConnectionA has a table lock on DeadlockTable2 and then tries to update DeadlockTable1. However, this cannot happen because connectionB has a table lock on DeadlockTable1, and it needs to run an update statement on DeadlockTable2. This scenario is a prime example why it is very important when designing transactions that you try to always update the tables you are using in the same order. If you are able to design your transactions in this manner, there is a much better chance that you will not have the headache of this scenario. The SQL Server database engine has specialized algorithms that notice these types of situations, and it will systematically close a process and allow the other one to complete.

Now that we have experienced a deadlock, I can use our SQLDiag tool to output the error to a text file. You may be wondering why you would want to view the fact that you had a deadlock error in a text file if you just saw it occur on your screen. Enabling the flags above will catch all instances of deadlock on your systems, which is very valuable information for a system with a lot of data modifications taking place. The ability to search a text file and see when the errors occurred and what objects were involved in the errors can save you hours and hours of research time.

To execute the SQLDiag utility, open a command prompt window and navigate to the \Binn directory in your SQL Sever installation. An example path to this file would be C:\Program Files\Microsoft SQL Server\MSSQL\Binn\.

There are a few parameters that you will want to use when running the utility. I will use the –E and the –O switches. The –E switch tells the utility to use integrated security when executing. The –O switch directs SQLDiag to output the file to the path you specify after the 0. An example of running this utility in the command windows would be:

C:\Program Files\Microsoft SQL Server\MSSQL\Binn
\SQLDiag –E –O C:\SQLDiagOutput.txt

Figure 1 features a sample SQLDiag.txt file. This particular screen capture shows the detail from the deadlock induced earlier. This output file shows the two separate SQL statements that caused the deadlock; you can also see the connection id (SPID) that the command was issued by. This information is very valuable in identifying and reengineering your processes that so that these errors occur less frequently.

Conclusion

Deadlocks, especially in SQL Server editions pre-2005, are often the bane of a DBA's existence. Without a good way to capture and retry the deadlock event, DBAs and developers have had to rely on SQL Profiler and other tools to capture deadlocks for research purposes. SQLDiag is a very useful tool for tracking not only these nasty deadlock errors, but for also capturing a lot of other diagnostic information for your SQL Server environment. I suggest that you take the time to not only follow the example in this article, but also explore the other features of this great tool, as a preventative measure for avoiding potential headaches in the future.

Tim Chapman is a SQL Server database administrator who works for a bank in Louisville, KY, and has more than 7 years of IT experience. He is also Microsoft certified in SQL Server 2000 and SQL Server 2005. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

About Tim Chapman

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

Editor's Picks

Free Newsletters, In your Inbox