BUG: Deadlocks Can Cause Tables to Be Stranded in tempdb

• Microsoft SQL Server, version 4.21a

SYMPTOMS

If a deadlock condition is proceeded by a level 0 error, such as a SQL Syntax error, then any temporary tables associated with that connection can be left stranded in tempdb. In addition, error 801 sev 20 can also accompany the deadlock.

801 Sev 20 Process %d tried to remove a resource lock; it does not hold on buffer 0x%lx-%S_PAGE

WORKAROUND

To workaround this problem reduce the chance of deadlocks in a system. Deadlocks can be reduced by eliminating bottle necks in tables where data contention can arise. In addition, if any unnecessary actions that cause level 0 errors can be eliminated, such as dropping a table that does exist, the problem will not occur.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 4.21a. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

During the normal running of SQL Server, deadlock conditions can arise, and normally cause no problem to any other process except the chosen deadlock victim. However, if the deadlock victim has a temporary table associated with it, and a level 0 error occur shortly before the deadlock, the associated temporary table can be left stranded in tempdb. This stranded table will not be dropped from tempdb until SQL Server is shutdown and restarted.