Article ID: 163908
Article Last Modified on 10/3/2003
APPLIES TO
- Microsoft SQL Server 6.5 Standard Edition
This article was previously published under Q163908
BUG #: 16592 (6.5)
SYMPTOMS
When you SELECT INTO temporary tables with identity columns, you may experience undetected deadlocks with the Checkpoint Handler in the database tempdb, and neither client is chosen as a deadlock victim.
WORKAROUND
To work around this problem, do any of the following:
- Run SELECT INTO temporary tables outside of manual transactions.
- Create the temporary table and then use INSERT SELECT instead.
- Remove the identity property.
- Use trace flag 5302 to disable holding locks on system tables.
STATUS
Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. 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 what the sp_who output looks like:
Spid Status Command Blocked
---------------------------------------------
4 Sleeping Dump Transaction 10
0 Sleeping Insert 0
Note that the spid 4 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.
Once this problem occurs, clients are unable to create temporary tables, and you can only stop the SQL Server with the Transact-SQL command SHUTDOWN WITH NOWAIT.
Additional query words: global identity
Keywords: kbbug kbfix kbother kbusage KB163908
