IIS 6.0 SQL 2000 Deadlock erros

Question

I have recently started a new job and they have a webserver that talks to a separate database server. It has recently been causing deadlocks errors when running ASP pages such as:

ERROR: MSSQL 2kTransaction (Process ID 103) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

The above error happens when the asp page is trying to access the MSSQL 2k database.

They think it is a server issue so I have been asked to deal with it. Any help would be greatly appreciated.

Answer 1

Usually deadlocks are an issue with either database design or inconsistencies with how the application accesses database resources. It's often that you will find resolutions to problems by making changes there, vs. the infrastructure. Deadlocks are often frequently not found during functional testing, but do tend to be found when you do performance and scalability testing.

You can help the development team out with this by using traces which cause more detailed info to be logged to the SQL log.

ex: DBCC TRACEON (3605,1205,-1) - make sure you turn that off (DBCC TRACEOFF) when you're done capturing log messages!

Next, verify the typical things with any SQL installation:

• ensure it's at SP4
• has proper hardware setup - log files on a raid 1 drive, data files on raid 5 drive, tempdb on a raid 1 drive, and backups on a raid 5 drive - i would call this a minimum for any real server.
• has a maintenance plan set up (backups, rebuilding of indices, integrity checks, updating statistics). I believe SQL 2000 has a maintenance plan wizard for this.

Lastly, begin plans to migrate to SQL 2008 R2 - SQL 2000 is at end of mainstream support now, and exits extended support in 2013.

Answer 2

You need to understand why deadlocks occur. Follow the guidance of the product documentation at Detecting and Ending Deadlocks and Troubleshooting Deadlocks.

How to solve the deadlock is impossible to say upfront, without the deadlock information. Usually, the solution is to change the data model (eg. add a missing index) and/or to fix the application design (change access patterns and queries).