Post reply

Need help on reseed Identity column - urgent..

  • July 10, 2007 at 8:27 am

    #93535

    Below is our project scenario:

     We have 11 tables [each with millions of data] joined by a partitioned view. The identity value is split across 11 tables. i.e first table will have seed=1 and increment=8, second table will have seed=2 and increment=8, ninth table will have seed= -1 and increment=8 and so on. By this way we have gap to add 5 more tables using the current identity value [identity column is of INT datatype]. In couple of tables the identity value is reaching its maximum INT value. We came with solution of reseeding since we have gap to add 5 more tables. No other solution seems to be successful with millions of production data with maximum allowed downtime of 2 days.

     Command we issued:

    DBCC CHECKIDENT (Claim_GC_1136_1zzz, RESEED,-2147483648)

     Current maximum Identity value in the table – 666666848. Obviously new identity value is smaller than the original. I know when we issue the above command without second parameter, the identity will be automatically reset to the current maximum value. But I do not want that to happen. Now my question is: When I reseed to negative value, is there any chance of seed getting reverted back automatically? In the above scenario how reliable is this solution?

     

    I am not getting any error message on executing the statement. I get something of this sort which seems to be fine.

    Checking identity information: current identity value '11', current column value '-999999'.

    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

     

    On strange behavior I could notice is:

    We checked the table immediate next day and there was no trace of what we did the previous day. Both the new identity value and newly inserted rows got disappeared!  But I should say that it doesn’t happen always. Rarely it is getting reset. Otherwise, the reseed which I executed two weeks back seems to be fine so far without any problems.

    I wanted to know how reliable this solution is. Am I missing something in the way I execute the command? Is there any chance of some database process resetting the seed automatically in order to maintain the table integrity?

  • July 10, 2007 at 9:31 am

    #718333

    Since you are reseeding to a value lower than the maximum value in the table, any future DBCC CheckIdent with the reseed option will automatically reset the current seed value to the maximum it finds in the table.  That could be what happened in the instance where it appears your work disappeared. 

    I've never personally tried to reset to a lower value with the intention of starting the sequence over at that value.  I would think this would potentially run into "duplicate" values at some point as it approaches the original first value.

    Other than all the work involved it might be better to simply change the data type from integer to numeric(x,x)?  I generally use numeric(10,0) which gives me a range up to 10 billion.  Of course in a production system this may not be an viable solution especially if it is the PK column of the table and hundreds of other tables reference it.  Plus any applications that reference the column might throw errors because the data type is no longer what is expected.  It can be done but it is a lot of work.

     

    James.

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply