How to reseed a SQL table with an “Identity crisis”?

You currently have a production database table that will not “swallow” any more data and you get the following error:

Arithmetic overflow error converting IDENTITY to data type INT

This error occurs as a result of the query is attempting to insert into the table residing in a Microsoft SQL database. The use if the ID column in tables and the data type for this field was INT allows a maximum value of 2,147,483,647. Once this value has been reached, Microsoft SQL will not allow a larger value to be inserted and throws this error.

The recommended solution would be to use the BIGINT data type which allows values up to 9,223,372,036,854,775,807. But there is another solution if altering the column is not option.

It will require the Identity value in the table reporting the error to be reseeded back to 1. This can be done with the following query:

DBCC CHECKIDENT ('<DatabaseName>.dbo.<TableName>', RESEED, 1)

Where <DatabaseName> is the name of your database and <TableName> is the name of the table encountering the issue.

Note: Before proceeding with the query, one will need to delete the existing rows in the database as to prevent possible scenarios with data handling due to the data now being out of the expected order.

If the information in the table/database is important to you or the organization, one may choose to export all data from the table, delete all the rows and then finally reseed the database. Then re-import the data (without the ID column) into the database.