Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

After migrating data from SQL Server 2000 to SQL Server 2008,when I am doing any a transaction in the table which find out more row on the database,that's gives me error.

 Transaction (Process ID 59) was deadlocked on lock resources with
 another process and has been chosen as the deadlock victim. 
 Rerun the transaction.

There are 3 trigger on the table.(Insert,Update,Delete).How can I solve this problem?

Thanks in advance.

share|improve this question
    
I think it may be too late to add a bounty once you have already accepted an answer? –  Gaius Apr 27 '11 at 11:05
    
@Gaius not by my experience on meta –  jcolebrand Apr 28 '11 at 16:25
    
@jcolebrand cool –  Gaius Apr 28 '11 at 19:02

3 Answers 3

up vote 2 down vote accepted

After upgrading to SQL Server 2008, did you run the below?

  1. DBCC UPDATEUSGAE
  2. UPDATE STATS WITH FULLSCAN
  3. REBUILD ALL INDEXES

The statistics blob is changed significantly from 2000 to SQL Server 2008 and the above steps are very important after the upgrade. I have seen many ppl run into performance problems but not deadlocks. I would try doing all of the above if you haven't so far and try to reproduce the problem afterwards.

That said, what is the results of SELECT @@VERSION on the 2008 box? Are you updating one row or several rows?

share|improve this answer
    
First of all,thanks for your answer.BTW , the result of SELECT @@VERSION --> (No column name) Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64) Apr 2 2010 15:48:46 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) –  Cell-o Apr 21 '11 at 13:32

I suspect that this may be related to the optimizers in the different versions handling lock escalation differently. Can you check for Lock:Escalation events?

share|improve this answer

IF you run Profiler and record all deadlock graphs then you can find out exactly which tables the deadlocks occur on, including pages. That should help narrow it down.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.