MS SQL, the most funny exception I have caught in my life :)

Hello everybody,

I like to do some experiments sometime, today I have caught a super cool exception 🙂

Just try to imagine: you are doing tests with super real time system which is working under heavy load and inserting a lot of rows same time. All is fine, all is cool.

One day you are getting in the log an exception and your system stops writing anything into the database:

Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

Very frustrating, right? 🙂 Especially if you are not familiar with MS SQL very much.

I have spent 2 hours to isolate the issue. Issue is very funny:

1. You have a table with IDENTITY column which means you have an auto incremented ID column, for instance.
2. You are inserting a lot of rows and deleting them rapidly.
3. Integer type in MS SQL has max value = 2 147 483 647

So, interesting question, what will happen if you add a new row, and auto incremented value becomes equal to 2 147 483 648?

Right, you will get frustrating exception in your code and will be debugging your application without a single thought that the reason of the issue, is simple, you have exceeded maximum integer value for your id in your table in your database..

How to struggle with this situation?

First of all, check what is current value of your auto incrementing id:

DBCC CHECKIDENT (‘your_table_goes_here’, NORESEED)

In my case, I have got following output:

Checking identity information: current identity value ‘2147483647’, current column value ‘2147483647’.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

You can simply reset this counter to 0 or value you need using following command:

DBCC CHECKIDENT (‘your_table_goes_here’, RESEED, 1)

Happy reseeding (:

Best regards,
Yahor