Article ID: 180101
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q180101
BUG #: 17762 (6.5)
SYMPTOMS
When you INSERT INTO temporary tables with identity columns while other clients are performing SELECT INTO on temporary tables, you may experience undetected deadlocks with the Checkpoint Handler in the tempdb database, and neither client is chosen as a deadlock victim.
WORKAROUND
To work around this problem, avoid creating temporary tables with identity columns.
STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
MORE INFORMATION
You can detect this situation by using the sp_who stored procedure and looking at the spid, status, command, and blocked columns. The following is an example of how the sp_who output looks:
Spid Status Command Blocked
3 Sleeping Dump Transaction 10
0 Sleeping Insert 0
Note that the spid 3 command changes from Checkpoint Handler to Dump Transaction when the database is set to Truncate Log on Checkpoint (which is always set for tempdb) and the Checkpoint Handler runs a checkpoint.
Additionally, if you run the sp_lock stored procedure, the blocking lock held by the client will be an exclusive page lock on syscolumns.
After this problem occurs, clients are unable to create temporary tables, and you can only stop the SQL Server with the SHUTDOWN WITH NOWAIT Transact- SQL statement.
Additional query words: block
Keywords: kbbug kbfix KB180101
