You are the database administrator for a banking company. You manage all the SQL Server 2008 databases ofthe company.
The company stores customer-related data in the database named Cust01. This database isaccessed by most users in the company for different purposes.
The users daily perform insert and updates to the database through a .NET application.
Eric, a user in the database, complains that his transaction has frozen and that he is not able to perform anyoperation in the database.
You find out that the problem is due to a deadlock.
You want to find out the user who isthe other participant in the deadlock by using SQL Server Profiler.
Which event should you trace?
A.
the Lock: Deadlock event
B.
the Lock: Deadlock chain event
C.
the Lock: Deadlock cancel event
D.
the Lock: Escalation event
Explanation:
You should trace the Lock: Deadlock chain event to detect the other participant in the deadlock. The Lock:Deadlock chain event is produced for each participant in the deadlock. The columns in the Lock: Deadlockchain event provide information, such as the ID of the two sessions participating in the deadlock, the IDs of theobjects involved in the deadlock, and the ID of the transaction in which the deadlock was detected. Tracing the Lock: Deadlock chain event will provide you with all the information necessary to identify the cause of thedeadlock. You should not trace the Lock: Deadlock event to detect the other participant in the deadlock. The Lock:Deadlock event contains information to track the transaction in the database that has requested a lock on aresource that was already locked by another transaction and caused a deadlock. This information is useful whendetermining whether performance of an application is being degraded by the occurrence of a deadlock. You cannot trace the Lock: Deadlock cancel event because this is an invalid event. The correct event is the Lock: Cancel
event. This event indicates that the process of acquiring a lock on a resource has been cancelled. You should not trace the Lock: Escalation event to detect the other participant in the deadlock. The Lock:Escalation event indicates the conversion of a higher level lock to a lower level lock. For example, a row-levellock has been converted to a table-level lock.Objective:
Monitoring and Troubleshooting SQL ServerSub-Objective:
Identify concurrency problems.References:
TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Locks Event Category > Lock: Deadlock Chain Event Class TechNet > TechNet Library > Server Products and Technologies > SQL Server > SQL Server 2008 > ProductDocumentation > SQL Server 2008 Books Online > Database Engine > Operations > Monitoring > MonitoringEvents > SQL Server Event Class Reference > Locks Event Category