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 triggers on the table (Insert,Update,Delete). How can I solve this problem?
-
@Gaius not by my experience on metajcolebrand– jcolebrand ♦2011年04月28日 16:25:19 +00:00Commented Apr 28, 2011 at 16:25
-
@jcolebrand coolGaius– Gaius2011年04月28日 19:02:45 +00:00Commented Apr 28, 2011 at 19:02
3 Answers 3
After upgrading to SQL Server 2008, did you run the below?
- DBCC UPDATEUSGAE
- UPDATE STATS WITH FULLSCAN
- 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?
-
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– Cell-o2011年04月21日 13:32:11 +00:00Commented Apr 21, 2011 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?
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.
Explore related questions
See similar questions with these tags.