dotnetrockz Posted April 18, 2014 Report Share Posted April 18, 2014 One of my colleagues shared the following experience. Recently, the identity column on one of his tables reached the maximum value allowable for an INT data type (2,147,483,647). Obviously, this caused all sorts of errors, as the application was unable to insert records into the table. This is a customer-facing table, so the issue needed to be resolved quickly. Converting the column to a BIGINT would require a significant amount of down-time. The solution? Reseed the identity column to -2,147,483,648 and have the values count upward toward zero. This took only seconds and the app was back up immediately. This is, of course, a temporary solution; it allows the team to schedule a more convenient time to resolve the issue. Here’s how to do this: DBCC CHECKIDENT ('tableName', RESEED, -2147483648); Link to comment Share on other sites More sharing options...
ravula Posted April 18, 2014 Report Share Posted April 18, 2014 Gp Thanks man Link to comment Share on other sites More sharing options...
kum5758 Posted April 18, 2014 Report Share Posted April 18, 2014 gp Link to comment Share on other sites More sharing options...
ghazni Posted April 18, 2014 Report Share Posted April 18, 2014 GP Link to comment Share on other sites More sharing options...
vikuba Posted April 18, 2014 Report Share Posted April 18, 2014 nice thinking... what if they run out of option on this one too? Link to comment Share on other sites More sharing options...
Suhaas Posted April 18, 2014 Report Share Posted April 18, 2014 That's the only possible solution kada except for changing the datatype to BigInt Either reseed the identity to 0 and decrement by 1 eachtime or or reseed the identity to -2147483648 and increment by 1 Link to comment Share on other sites More sharing options...
macha Posted April 18, 2014 Report Share Posted April 18, 2014 nice thinking... what if they run out of option on this one too? fix for time being not permanent fix... Link to comment Share on other sites More sharing options...
loveindia Posted April 18, 2014 Report Share Posted April 18, 2014 nice thinking... what if they run out of option on this one too? This is a good option for now man.. If they are gonna run out of these numbers soon they will fall in the soup.. Instead of that they can remove the is identity property of this table so that the table will not have identity column and add a new one with bigint or copy the data in increments to a different table and finally switchover to this table.. There will be no down time as we'll in this approach. Switchover in increments i m telling only because if there a lot of tables holding this as a foreign key then there will be no impact and it time consumption factor.. Switching to bigint is good because it will accept values between 2^-31 to 2^31. Link to comment Share on other sites More sharing options...
loveindia Posted April 18, 2014 Report Share Posted April 18, 2014 If you have fk from this table, it won't stand good with direct reseed.. But I don't think that's the case for now at least with you guys. Typing from phone ignore typos Link to comment Share on other sites More sharing options...
Recommended Posts