April 21, 2011 at 11:34 am
Hi Guys,
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.
April 21, 2011 at 12:39 pm
Total CPU physical and logical
Total RAM
Total Drives not partitions
paste table script as well as trigger and indexes if on this table
select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.cpu > 100
order by sp.cpu desc
paste the result of this query when performance going down
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
Regards,
Syed Jahanzaib Bin Hassan
BSCS | MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
www.aureus-salah.com
April 22, 2011 at 9:59 am
Selahattin SADOGLU (4/21/2011)
Hi Guys,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 withanother 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.
Finding the root cause of deadlocks is a complicated topic...and a system that is running fine can suddenly start experiencing deadlocks after a hardware upgrade, or after making any slight change to the system, which is one reason they can be very hard to detect and fix. One thing we now have in SQL 2008 that we did not have in SQL 2000 are Extended Events. This allows us to retrieve the Deadlock Graph for all deadlocks, even after they occur. Examining Deadlock Graphs is one way to determine which processes were involved in the deadlock and what SQL was being run on each. That is where I would start. Here is a good article on the topic: http://www.sqlservercentral.com/articles/deadlock/65658/[/url]
If you post again and attach your Deadlock Graphs we can take a look and try to help determine which pieces of code are creating the issue, and maybe how to fix it.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
April 26, 2011 at 2:32 pm
Syed Jahanzaib Bin hassan (4/21/2011)
Total RAM
Total Drives not partitions
paste table script as well as trigger and indexes if on this table
select st.text,sp.* from sys.sysprocesses sp
cross apply sys.dm_exec_sql_text(sp.sql_handle) st
where sp.cpu > 100
order by sp.cpu desc
paste the result of this query when performance going down
Regards,
Syed Jahanzaib Bin Hassan
MCTS | MCITP | OCA | OCP | OCE | SCJP | IBMCDBA
My Blog
http://www.aureus-salah.com[/quote%5D
Hi,
Thanks in advance.
May 2, 2011 at 11:40 pm
Enable TRACE FLAG 1222 . This will output the deadlock trace to SQL Server log file.
Try to understand why it occured.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply