Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

find out which queries are causing the deadlock Expand / Collapse
Author
Message
Posted Monday, May 5, 2014 6:55 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:52 AM
Points: 454, Visits: 194
Hi, I occasionally get the following message on our production sql server box.

Transaction (Process ID 787) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock.

I can find out the query that was chosen as the victim. How can I find out the other query that is part of the deadlock? Also, how can we fix this?
Post #1567489
Posted Monday, May 5, 2014 8:41 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Sunday, November 23, 2014 12:50 AM
Points: 3,109, Visits: 11,515
This will log deadlock info to the SQL Server error log:
print 'Set on Trace Flag 1222 to log deadlocks'
dbcc traceon(1222,-1)
print 'Show all trace flags that are on'
dbcc tracestatus(-1) with no_infomsgs

Post #1567542
Posted Monday, May 5, 2014 9:52 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Thursday, June 5, 2014 7:52 AM
Points: 454, Visits: 194
Hi Michael, Do I do this when I have a deadlock and turn it off again after I find the issue? Is there any performance penalty because of this?
Post #1567578
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse