deadlock

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » Administering » deadlock

deadlock

Rate Topic

Display Mode

Topic Options

Author

Message

charipg

Posted Monday, September 21, 2009 5:39 AM

Old Hand

Group: General Forum Members
Last Login: Thursday, May 16, 2013 12:43 AM
Points: 378, Visits: 897

how to findout the deadlock happend?
how to resolve it?

Post #791038

Sergey Vavinskiy

Posted Monday, September 21, 2009 6:05 AM

SSC Veteran

Group: General Forum Members
Last Login: Monday, October 15, 2012 7:17 AM
Points: 201, Visits: 378

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

Henrico Bekker

Posted Monday, September 21, 2009 6:30 AM

SSCommitted

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 2:24 AM
Points: 1,871, Visits: 2,692

Sergey Vavinskiy (9/21/2009)

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

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

Grant Fritchey

Posted Monday, September 21, 2009 6:35 AM

SSChampion

Group: General Forum Members
Last Login: Today @ 3:04 PM
Points: 13,375, Visits: 25,159

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 »

Permissions