Microsoft KB Archive/198572

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

Article ID: 198572

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q198572



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 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: kbbug kbpending KB198572

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.