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

deadlock Expand / Collapse
Author
Message
Posted Monday, September 21, 2009 5:39 AM
SSC-Addicted

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

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 5:52 AM
Points: 412, Visits: 1,011
how to findout the deadlock happend?
how to resolve it?
Post #791038
Posted Monday, September 21, 2009 6:05 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, May 22, 2014 9:05 AM
Points: 201, Visits: 391
Here are a couple a good strategies:

http://weblogs.sqlteam.com/mladenp/archive/2008/05/21/SQL-Server-2005-Immediate-Deadlock-notifications.aspx

http://www.mssqltips.com/tip.asp?tip=1036
Post #791044
Posted Monday, September 21, 2009 6:30 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, July 17, 2014 6:43 AM
Points: 1,904, Visits: 2,820


You can choose, do you want to know the process, or just catch it to investigte?

1. Traceon can cath your deadock info so you can investigate and maybe correct some bad coding.
2. get te SPID of the blocking process, and use it in DBCC INPUTBUFFER(spidid in here)

example:
DBCC INUTBUFFER(234)

Result will show the query running/was running..



----------------------------------------------
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Post #791053
Posted Monday, September 21, 2009 6:35 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Today @ 5:33 AM
Points: 15,494, Visits: 27,877
It's a very good idea to turn on TRACEFLAG 1222. It can be difficult to interpret, but at least you're assured of capturing the information as each deadlock occurs. Otherwise, in order to capture the information, you'll need to run a trace 24/7. This can be a bit of a pain to maintain.

To set the traceflag on:
DBCC TRACEON (1222,-1);



----------------------------------------------------
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood..." Theodore Roosevelt
The Scary DBA
Author of: SQL Server 2012 Query Performance Tuning
SQL Server 2008 Query Performance Tuning Distilled
and
SQL Server Execution Plans

Product Evangelist for Red Gate Software
Post #791062
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse