FIX: Thread Deadlock Causes Checkpoint to Hang in tempdb

The information in this article applies to:

• Microsoft SQL Server, version 4.21a

BUG# NT: 9992 (4.21a)

SYMPTOMS

When you execute tempdb-intensive queries involving sorting, under very narrow conditions, a thread deadlock can occur during sort cleanup. This is often difficult to identify because the visible symptoms are ambiguous. However the observed characteristics include a general slowdown, increased blocking, and the checkpoint process hanging in tempdb. It is often possible to login to SQL Server with ISQL.EXE, do SELECTs from user databases, but not possible to run data modification statements in tempdb.

CAUSE

Worker threads become deadlocked when deallocating extents during sort cleanup.

WORKAROUND

The problem is exceedingly narrow in scope and very rare. Because of this and the ambiguous symptoms, this problem should not be suspected until close study has ruled out all other problems. This would include verifying dbcc newalloc and dbcc checkdb are error free on all databases, no errors exist in the SQL errorlog, and any observed blocking or slowdown is not caused by conventional concurrency issues. Slightly changing the application, queries, platform, or SQL configuration may avoid the problem.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 4.21a. This problem was corrected in the latest U.S. Service Pack for SQL Server version 4.21a. For information on obtaining the Service Pack, please contact your primary support provider.