These are the ints that never die, we reseeded negative.

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.

Say what you mean, tell me I’m right and reset the identity….

Today I wanted to change the number in my identity column to be way higher. My plan is to union my new table with another table that has a low id and be able to tell what is coming from where.  I know that my old table will never got over 100,000 records, but to be safe I set the number in the new table to start at 700,000.  This is called seeding the identity.  You can also reset your identity this way.  Use caution when you are doing this because if you reseed the identity and it is lower than an existing identity in the table, it will complain when you hit that identity if you are inserting.   Here is my example:

DBCC CHECKIDENT ([Schema.TableName], RESEED, 700000)

Happy Planting!