Hi

Can anyone help by telling how to resolve deadlocks step-by-step.

I guess atleast some one is good in deadlocks.

Thanks
P

Hi,

I have few instances that deadlock is occuring due to redundant indexes.
Identify the index/object causing the deadlock by enabling the
trace flag 1204. This will enter the details of deadlock in sql server error
log.

How to set tehe trace flag:-

DBCC TRACEON(1204,-1)

There are various other reasons as well for deadlock.

see the below links for more info:-

http://www.devx.com/getHelpOn/10MinuteSolution/16488/1954?pf=true

http://sqlteam.com/forums/topic.asp?TOPIC_ID=49689

http://www.sqlservercentral.com/columnists/skumar/tracingdeadlocks.asp

Thanks
Hari
SQL Server MVP


Show quoteHide quote

Hi hari

I am new to sql server
I have a question.
1)when can we use trace flags i.e dbcc traceon(1204,3605,-1)?

  after locking occured or before locking.

2) when can we use Sql profiler trace on?
  after locking occured or before locking.

  if we have to use before then it always have to have run 24 hours
traceon then where we will store that trace profile?

Thanks Hari.

I just got my answer.
Thanks a lot for sending that link.

1. be sure to always access objects in the same order
2. keep transactions as short as possible
3. never do selects inside of a transaction
4. to help find cause of blocking search for sp_blocker script on google and
use it
5. add nolock hints to selects where you can



Greg Jackson
PDX, Oregon

Thanks Jaxon

Veritas Backup Exec. 9.1 SQL Agent - Use of...
Differential and Transaction backup restore.
SQL brain twister
Maintanence Plans for Backups
List all the logins that have a certain privileges
SQL Server EM/Query Analyzer Access
Error 21776 [SQL DMO]
Log shipping question
blocking alerts
Re: Strange problems accessing SQL server with windows 2000 window