So to avoid the problem, should I use tab lock for every statement in the transaction (update, delete, then update again)? Or should I just set a different isolation...

The reason why I thought it's a deadlock is 1)There were messages in our log about deadlocks on this process, and some processes have been chosen to be killed by...

Thanks everyone for the reply.... I will turn on the trace flag, but it is very hard to reproduce as we don't often have two of these processes running together...

Both tables B and C do not have indices because they are very small temporary tables.... This problem is rare to happen because ususally there are not too many...

I wish I have seen this earlier.... I kind of tried to update the database ID of newly attached tempdb to 2 but then hesitated if it will still leave something...

I have detached it with the flag 3608(stupid me).  Stopping and restarting didn't help.  Then I spent the whole night reinstalling sql-server and restored all dbs.... 

I guess it's a...

Thanks for the input guys... However,  the IDs passed from a list from front end and are not stored in a table nor they are in sequence.... Also we would...

Currently we are detecting the health of the SQL server service using WMI.  Just wonder if there is anything else we can do, such as SNMP?  Has anyone used SNMP with SQL server 2000?