Knowledge Base

FIX:Recovery May Not Reset IDENTITY Value After TRUNCATE TABLE

Article ID: 147408

Article Last Modified on 3/26/1999

Microsoft SQL Server 6.0 Standard Edition

This article was previously published under Q147408

BUG# 13135 (6.00)

SYMPTOMS

The current value for a truncated table is not the seed value after a restart of SQL Server.

CAUSE

If recovery has to recover INSERTs done prior to a TRUNCATE TABLE, then the reset seed value is not recovered correctly. This would normally only happen after an unexpected shut down of SQL Server.

WORKAROUND

Perform a CHECKPOINT before the TRUNCATE TABLE to flush all outstanding IDENTITY information. Or, you can perform a single INSERT after the TRUNCATE TABLE and before a shutdown can occur.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.0. For more information, contact your primary support provider.

Additional query words: sql6 identity truncate

Keywords: kbusage KB147408