<\/p>\n
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:<\/p>\n
Arithmetic overflow error converting IDENTITY to data type int.<\/em><\/span><\/p>\n Description:<\/strong> 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.<\/p>\n <\/p>\n Note:<\/strong> 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.<\/p>\n <\/p>\n 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.<\/p>\n In our case, the actual interruption would occur if there was a loss of access to the existing records.<\/p>\n 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.<\/p>\n <\/p>\n 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.<\/p>\n 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.<\/p>\n 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.<\/p>\n <\/p>\n 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.<\/p>\n In fact, it was basically an unnecessary field, and ironically the application had been partially interrupted due to an unnecessary field.<\/p>\n We decided to set the value in this area to -2.147.483.648.<\/p>\n 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.<\/p>\n Without downtime, risk and code change on the application side and effortlessly.<\/p>\n 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.<\/p>\n <\/p>\n Of course, every problem has its own conditions and every problem cannot be solved in the same way.<\/p>\n When you encounter these and similar problems, you should not interfere to the problem with the first thing that comes to mind.<\/p>\n You should consider the Problem in many ways and find and implement the most efficient solution.<\/p>\n This approach is an indispensable requirement especially for critical systems that need to work 24\/7.<\/p>\n\n