BUG: IDENTITY Out of Sync If Server Is Shut Down w/o Checkpoint

ID: Q198572

The information in this article applies to:

Microsoft SQL Server version 6.5

BUG #: 18468 (SQLBUG_65)

SYMPTOMS

The "current value" for an IDENTITY property for a column can become out of sync if the server is shut down without a checkpoint. If the identity column is a primary key, you will encounter the following error message when an INSERT statement runs:

Msg 2627
Violation of %s constraint '%.*s': Attempt to insert duplicate
key in object '%.*s'.

If the column has a unique index, you will receive the following error message:

Msg 2601
Attempt to insert duplicate key row in object '%.*x' with unique
index '%.*s'

One of the more common symptoms occurs when you execute DUMP and LOAD Transact-SQL statements. This happens because the server automatically inserts rows into msdb..sysbackuphistory and msdb..sysrestorehistory when either a DUMP or LOAD statement executes. These tables use an identity column as a primary key.

When a DUMP or LOAD command runs and encounters this problem, the DUMP or LOAD operation will be successful, but you receive the following error messages. Note that this is an example of the messages you would receive when the LOAD statement runs.

Msg 2627, Level 14, State 1
Violation of PRIMARY KEY constraint 'pk_restore_id': Attempt
to insert duplicate key in object 'sysrestorehistory'.
Msg 3009, Level 16, State 4
Could not insert a backup or restore history/detail record in
msdb.dbo.sysbackuphistory or sysrestorehistory. This may indicate
a problem with the MSDB database. DUMP/LOAD was still successful.

The error log will contain the following message:

A history record could not be written to msdb.sysbackuphistory
or msdb.sysrestorehistory

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5.

MORE INFORMATION

If you encounter this problem, the DBCC CHECKIDENT statement can be used to correct the identity column value.

This problem does not occur in SQL Server version 7.0.

Additional query words: prodsql

Keywords : kbbug6.50
Version : winnt:6.5
Platform : winnt
Issue type : kbbug