A few weeks ago a client called around 11:00 pm and said that they received an error in a very critical Microsoft SQL Server environment, on a very critical table as follows:
Arithmetic overflow error converting IDENTITY to data type int.
Description: If you get this error, you are unable to add a new record to a table that has the data type Integer and has a Identity property. The data range that the integer data type can hold is from -2.147.483.648 to 2.147.483.647.
Note: You may also receive a similar error for columns defined by the Smallint or Bigint data type. Of course, the data type at the end of the error message will change.
The related table was a table that could not tolerate the interruption. Although application could not insert new records, the system could also work with existing records.
In our case, the actual interruption would occur if there was a loss of access to the existing records.
And this error did not cause the existing records to be reached. But since the new record could not be added to this table, the problem had to be solved.
The first thing that came to my customer’s mind was to convert the data type to BIGINT, but when we think of the circumstances, it was a bad option.
Because the Identity field was the Primary Key and the Clustered index, and because this change also affected the application codes, the code had to be modified as well.
This was a process that would require interruption, and because it was a very basic table, it was used in many parts of the application, and it would take a long time to change the codes.
When I looked at the smallest value of the Identity area, I saw that it was 700 million. After our conversation with the software developer and manager, I learned that the values held in this field have no meaning, are not shown to the end user and are not used anywhere.
In fact, it was basically an unnecessary field, and ironically the application had been partially interrupted due to an unnecessary field.
We decided to set the value in this area to -2.147.483.648.
Because these values had no meaning, and after a single metadata operation, at least 2.8 billion new records could be added to the table.
Without downtime, risk and code change on the application side and effortlessly.
As a result, we implemented this solution and our problem was solved. As we thought, no downtime or error occurred, no one needed to change application codes.
Of course, every problem has its own conditions and every problem cannot be solved in the same way.
When you encounter these and similar problems, you should not interfere to the problem with the first thing that comes to mind.
You should consider the Problem in many ways and find and implement the most efficient solution.
This approach is an indispensable requirement especially for critical systems that need to work 24/7.
