SQL Server 2000 Deadlock analysis

SQL 2000:

1.Enable trace 1204 to capture the deadlock in the SQL Server's error log.

DBCC TRACEON(3605, 1204)
or
DBCC TRACEON(3605)
DBCC TRACEON(1204)

To turn them off , use

DBCC TRACEOFF(3605, 1204)

From the log, you find out the contented resource the first. 

For page lock, use dbcc page() to figure out the involved the objects.

dbcc traceon(3604) --enabled the page header

dbcc page(db,file,page,1)

dbcc traceoff(3604)

use sysobjects, sysindexes to find out the involved objects.

http://msdn.microsoft.com/en-us/library/ms178104(v=sql.105).aspx 

2. Use SQL Profiler to capture the deadlock.it's better to also capture other information to assist analyzing the deadlock. usually, that includes these ones:

Locks: Lock:Deadlock and Lock:Deadlock Chain
Stored Procedures: RPC:Completed, RPC:Starting, SP:StmtCompleted, SP:StmtStarted
Transactions: SQL Transaction
TSQL: SQL:BatchCompleted, SQL:BatchStarting, SQL:StmtCompleted, SQL:StmtStarting

From the deadlock it detected, you can figure out which two SPs are involved. Then analyse the

statements from each SP sorted by their sequence, you probably can find out which two statements

are conflicting.

This approach is not quit useful.