Re: Identity column re-using existing numbers?

[Evan Harris <auctionitis@xxxxxxxxx>]

I have seen this before (primarily with backups taken on a replicated
database) How did you do the migration ?

What I have seen is case(s) where records are replicated and applied to a
target database on a remote machine but because of how the write on the
remote side is performed the identity column does not get incremented.
Obviously the replicated record contains the identity column value so
either the write somehow bypasses identity column checks (maybe by turning
off a constraint or the identity column attribute is turned off while
replication is being applied. I don't know the exact mechanism but
conceptually something like that happens to stop the database incrementing
the identity column. The counter for the identity column remains at
whatever it was when it was "turned off".

Later when tables with identity columns are restored from a backup taken on
the target system the identity column start value has to be set to the
current maximum + 1 to avoid duplicate key errors.


On Wed, Dec 5, 2018 at 8:18 AM Matt Olson <Matt.Olson@xxxxxxxx> wrote:

> We migrated some of our tables to another partition for testing and we
> have noticed that their identity columns which should generate the next
> available number is infact generating an existing number and failing the
> primary key check. Has anyone see this before?
>
> There some easy command to restart identity value to the next available
> number?
>
> We are mystified why this didn't come over during the restore of that
> file...
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: https://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at https://archive.midrange.com/midrange-l.
>
> Please contact support@xxxxxxxxxxxx for any subscription related
> questions.
>
> Help support midrange.com by shopping at amazon.com with our affiliate
> link: https://amazon.midrange.com