Recently we had a system go down because we ran out of integers. (Mental note to create an alert when we are getting near to running out of integers.) If we upped the column to a bigint we were going to have to drop all the indexes. This server doesn’t have the capacity needed to do an operation of that size and it was estimated that it would be down for 8 hours while we dropped indexes, upped the column type and added back the indexes. This was way too long as it was early in the work day (had it been evening, it would have been fine).

There was also a concern about how many stored procedures were expecting an int but would need to be modified to a bigint, along with any code. That was a big undertaking and we were in an emergency down.

We talked about adding an additional table to take over but again, we were going to need to adjust a lot of things. During our discussion, I was reviewing this awesome blog post by Ed Pollack.

We decided that the fastest temporary solution was to reseed starting with the the smallest of negative ints -2147483648. Our table was already counting up with an increment of 1 and so we picked the smallest of numbers. That means we will seed with -2147483648, then go to -2147483647 and up to 0. We will have to watch closely as we approach 0 to not spill over to the positive numbers that have already been used.

This is only a temporary solution because of that spill over. Our real solution is to do the work and change that column to a bigint.

Here is a sample of what our code looked like:

DBCC CHECKIDENT ('dbo.MyTable', RESEED, -2147483648);

Because we chose this option, there was no need to drop indexes or fix code, but we are in the process of getting all of that ready for a production release.

Within seconds of reseeding, we were back online and working. It was absolutely a day I will remember.

The song for this post is Avicci’s The Nights.