SQLTeam.com | Weblogs | Forums

Deadlock problem, Trace Log

We are getting a nasty deadlock problem.

I've got the Server Error log and I'm looking at the Deadlock trace dump from the Server log. The same table and stored procedure shows everytime, which is good. And it shows how the processes are owning one resource and waiting on another resource. And it shows for each process involved, the statement involved.

My question is, the statement it shows, is that the statement where the process acquired the resource, or where the process is waiting for the other resource? Is there a way to determine both statements?

Using the DBCC TRACEON can help you to detect deadlock in SQL Server, See here for more info: http://www.sqlservercentral.com/blogs/zoras-sql-tips/2016/07/04/know-how-to-handle-deadlock-in-sql-server/

Thank you.

Yes, we already had the deadlock information from the SQL Log. My question is. The SQL statement that appears in the deadlocking information dumped to the SQL management log, is that the Statement where the Process acquired the Resource and is blocking the other process? Or is it the statement where it is requesting the resource and is being blocked by the other process?

When there are 2 processes involved in the deadlock, it shows me 2 SQL Statements, one per process. Shouldn't it be displaying 4 SQL Statements? Where it acquired the resource AND where it's requesting the other resource, for each process.