My identity values has jumped how to set it normal

[Former Member]

Please can you someone help me in the steps in setting the identity value back

I have the table which has a identity column and it was good untill 2 days back and now suddenly the value has bumped up by 500000000 and giving its own values to the identity thing which should have ben sequential.

Please can some one give the steps to set it back

is it

1)bcp out the values

2)run sp_changeattribute tablenmae,"identity_burn_max", 0, "[new_value]"(new values some value bigger than the preseent row count)

3)then bcp in the values back

plz let me know if thats the solution

Its an urjently required

version is 15.7 and non prod environment.

Thanks

[former_member247744]

You may want to check out KBA 2122114 - Identity start value wrong after load of database and transaction logs

[former_member188958]

The easiest thing to do is probably to update the value in the rows that are reflecting the bump, then use sp_chgattribute to reset the new burning set value.

Start by backing up the database so that you can recover if you make a mistake.

Figure out the largest value used before the jump in values occurred.

Determine the smallest value generated after the jump occurred.  The difference between these two is the actual size of the gap

Issue the sql "set identity_update <tablename> on"

Then update the table setting the value of the identity column equal to itself minus the actual size of the gap.

Then "set identity_update <tablename> off"

Then determine the max value for the column and use that with

sp_changeattribute to set the identity_burn_max value for generating the next value.

Then, given that you don't want large gaps, configure this table (and any others where you want no large gaps) to have a small gap with

sp_chgattribute "table_name", "identity_gap", set_number