SQL Server - Reseeding Identity Value

Recently we came across an issue where a table was not accepting any new records. Upon further inspection we discovered that the table had an identity key of type INT and it has reached its limit. An INT datatype is 4 byte large and as such can hold a maximum of 2147483647 rows. The table in question only had about 300,000 rows.

An identity field is an auto generated number and the default increment is 1, so in a table where you simply insert or update records, you should technically never run out of available values for INT datatype or it will take a long long time. But, let's assume you inserted 1000 records in this table, and then deleted 500 of those records. Because identity value is incrementally generated, the next identity value will be 1001, even though your table only has 500 records. Precisely this is what happened with this table. At most, about 2000 rows were inserted in this table, but the data was inserted and re-inserted throughout the day. For example, 2000 rows may be inserted in the morning, then after an hour, same 2000 rows were inserted again, deleting the first 2000 rows etc. So, during every insert even though the number of records were more or less same, extra 2000 identity values were being eaten up. In about a day close to 20,000 identity values were used for just 2000 entries.

Extrapolate that to several years and you can imagine why the table was running out of values. There are couple of ways to fix this problem, but it largely depends on how complex your database schema is, i.e. whether this identity key is also used as a foreign key in some other table(s) etc. Fortunately, this table was stand alone, so it was rather simple.

A temporary solution to this problem is to find a contiguous block of unused identity values. For example, let's imagine your table has 1,000,000 rows but the 1st record's identity value is 500,000. Which means you can reseed your table to restart your identity keys from value 1 again by running the following DBCC command...

DBCC CHECKIDENT (YourTableName, reseed, 0)


Once you run this command, the next record in this table will start from 1. But, as I said, this is a temporary solution because once the identity value reaches 499,999 in the above example, it will error out when you try to insert next record, because identity value 500,000 is already present.

But what if you couldn't find contiguous empty identity values. If you can afford to keep database down for a few hours, here is what you can do...

• Disconnect users from the database (with proper notification of course) and bring it in single user mode.
• Create a new table with the same schema (name it something other than your table in question) and then write a query to move all the records from old table to this new table (except the identity value) which should reseed the identity value in this new table and all the records will be inserted in contiguous fashion, starting from 1. 
• Once you have completed the move, rename or drop the old table (I would rename it at least until the dust has settled) and rename the new table to the same name as the old table.

But, what if you can't afford to keep your database down for more than a few minutes? It gets little tricky, but it is still doable. Here is what I would do...

• Switch the database to single user mode.
• Rename the current table to something else. 
• Create a new table with the same schema and name it same as the old table.
• Bring the database back up so that the users can start using it and all the new records will not be added to this new table starting from identity value 1. (Old data is still not available).
• Write a query to move the data from old table to the new table (ideally you would want to create a scheduled task that can be run when the load on the database, especially on this table is low to avoid deadlocks). You can also play with query wait times or CPU resource governor but I would recommend the following...
• Normally I wouldn't recommend this, but since this is a background operation and the key is to lock the table for the least amount of time, I would create a FAST_FORWARD cursor to run on the old table and insert one record at a time in the new table. This, coupled with scheduling during low load will prevent or at least minimize deadlocks.

There may be other ways, but this has worked for me. Also, if you want to check the current identity value of your table, you can run the following command

DBCC CHECKIDENT (YourTableName)

While these are workarounds, deleting and reinserting the same data is never a good idea. Also, it doesn't beat a good database design. Don't use identity key if you don't have a particular reason for it and also if you must use it then ensure proper data type (SMALLINT, INT or BIGINT) depending on the potential table size.

Thank you.