SQL STATE: 40001, NativeError: 1205, Deadlock Victim

jana
jana used Ask the Experts™
on
We are getting an error when a station saves to the database on the server (SQL).

The error is:

     Error #-2147467259
      Transaction (Process ID 64) was deadlock on lock resources with another process and has been
      chosen as the deadlock victim. Rerun the transaction.
     deadlock victim. Rerun the transaction.
     (Source: Microsoft OLE DB Provider for SQL Server)
     (SQL State: 40001)
     (NativeError: 1205)
     No Help file Available

The workstation software is "Microsoft Retail Management System POS" connected to the server.

The server is a Dual Core 2.4, 2 cpu, 4gb ram, 280 hard disk, windows 2003 with SP1 and SQL 2000 with SP3
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
This is a complex issue with many potential causes.

The first thing I would try is stopping and restarting the database server (not the physical machine, just stop and start the database service). If you can reproduce after that, post the specific steps involved.

If you have already done this, the next step would be to restart the database and run the profiler until you can see the error.   You can also check the monitor to see what statement is being executed by the process id mentioned in the error (in this case, 64). Look for the table being updated/deleted from and see if other processes are issuing SQL against that table (deadlocks usually happen when two different processes try to access the table in slightly different ways. They are not usually caused by the same SQL statement being run in sequence by different processes - at least that is my understanding).

 


Commented:
Hello Ramante,


Setting DBCC TRACEON (1204) would help. After this, you can see actual SQL statements, that caused a deadlock.


Take a look at this:
Finding and troubleshooting SQL Server deadlocks
http://www.mssqltips.com/tip.asp?tip=1036

Regards,

Rimvis

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial