Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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.

share|improve this question
up vote 0 down vote accepted

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.

share|improve this answer
it did not have sp4 installed! – Skeep Jun 25 '10 at 12:20

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).

share|improve this answer

Your Answer


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.