Running Out of INT

 

Has this every happened to you? Your legacy application database is humming along nicely, query requests happen with little to no blocking, users are happy with performance, nary an issue in sight. Then out of nowhere new records are failing to be inserted! Your once happy database has been brought to a screeching halt. Why is that? Oh no, you've got a table with an INT datatype. What's worse is it's the primary key! And it's your orders table!! Extreme example? Perhaps, but willing to bet it happens.

The Problem

Let's take a moment here just in case someone reading is unfamiliar. In many systems I've worked with, a table will have a Primary Key that is an INT (integer) data type. Most frequently an ID field. Not something you'd necessarily query for or put into a report, but it's a means of identifying a record and helping to speed up searches, an effective column to join other tables on, perhaps a foreign key here and there too. Ninety Nine percent of time, it's a good bet that the field is setup with the Identity (1,1) configuration too. Meaning the records inserted to the table will start at a value of 1 and increment by 1.

The INT data type has a maximum value of 2,147,483,647. Now at first glance, that feels like a huge, never achievable number. Who wouldn't love to have 2 billion orders? However, if that table has many years of history or even better is keeping an event log, that could definitely get hit. Per my opening paragraph example, you don't want to get caught napping when that next record attempts to be inserted.

Let me lay out an example I encountered a couple of years ago. I work with an application that was doing this very thing. There was an event log table with an ID column set as the INT datatype and it was growing rapidly. This app was developed many years ago with little thought as to how much it would grow and be used. One day, it suddenly stopped logging and throwing all manner of errors. We discovered the event table was maxed out. 

The Solutions?

You might say, oh just change the data type to BIGINT (a maximum value of 9,223,372,036,854,775,807 by the way). Sure, sure....do you know what happens when you try to change a data type on the fly? SQL Server has to go in row by row and convert that value and write it back to the table. Any idea how long that would take or what it would do to your TempDB, memory or indexes? No. Thank. You.

Purge old rows and reseed the Identity? Alright, and those other tables that reference THIS ID field? Orphan records or even worse, next time you query you get duplicate results since you'll have used the same ID value again. Heaven help you if you have Foreign Keys on that column. Unless you turn off the check constraints, the engine then has to go verify that the referenced value is also gone. Guess how it does that? Row by agonizing row (RBAR for short). Nope, that's out too.

In our case, the super quick fix to get the application back online and operational was to change the Identity setting to (-2,147,483,647,1). That's the maximum negative value you can set. Since a negative number is still an integer. It would start there and increment by 1 again. That is until you reach 1. Then you're really stuck. However, do you like seeing negative values in your data? Even if it is just an ID field? I do not. Nevertheless, we needed the app back online and quickly so the fix was in.

Our End Solution

Naturally we needed a table with the BIGINT datatype AND we need to retain all of the history. To accomplish this, we first created a new table that was a duplicate schema of the original with the exception of the ID field being BIGINT and we set the Identity Seed values to (2,147,483,648, 1). Thus ensuring any new records going in would be beyond our existing maximum value. We named that table OrigTableName_BIGINT (redacting the actual table name of course). Next, we renamed the original table to OrigTableName_Old. I should point out the application and associated services had to be stopped for this since otherwise the table would have been in use and unable to be renamed.

Next up, we created a view that was simply a UNION between both tables and it was name OrigTableName. This way, if the application used it to get history, it would be able to find existing records. Or in inserting new records it would insert them to the OrigTableName_BIGINT. This was simply a band aid to get the app functioning and using positive numbers again.

Finally, I setup a nightly agent job to move batches of records in increments of 10,000 from the _OLD to the _BIGINT table. It would convert the INT to BIGINT upon the insert. After copying over a batch of 10,000 rows, they would be removed from the _OLD table until the record count was zero. This operation took about two weeks as we had to run it after hours and it had a timer of only 6 hours each night.
After all of the records were moved out, the _OLD table was dropped, the view was dropped and the _BIGINT table renamed to simply OrigTableName. Bada bing, we're back in business.

Is This Still An Issue?

In modern database applications, we don't see the INT datatype in scenarios such as this much anymore. In the early days of computers, storage was expensive. So they attempted to cut corners where possible to save byte space. Making an INT datatype (in theory) reduced potential storage overhead. Since the early 2000's though, storage got ridiculously cheap and these cost saving measures are just not necessary. Thus most new products that would use this type of Identity setup are using BIGINT. In fact, anytime I setup a new table for a process or job, even if I know there's no way it could possibly ever hit 2,147,483,647, I set that datatype to BIGINT just in case.

I Want to Help You!

I wrote this blog today with the notion that maybe someone will read it who's using older legacy systems and take note to go have a look at their databases. See if any of those INT data types exist. I certainly do not want any of your production systems to hit this snag at a critical moment. Or overnight. I wouldn't want a phone call at 2 a.m. and with my sleep deprived brain try to finagle a solution.

To that end, I have a simple yet effective script available to you dear readers! This will check all of the tables in a particular database looking for the INT datatype and what its maximum value is today. This is but one component of a job I have running daily in our environment. Coupled with Powershell, I feed it a list of servers and database names. Then with a foreach loop, I execute this script against each instance and database dumping the results to a central table. There's a daily report I run that shows what my current value is today, what the value was yesterday and how many records were inserted. I can watch for a trend now. So if for example all of a sudden this particular table is growing by 100,000 records a day, I can start looking at possibly running a similar exercise that I laid out above. 
Most of what I find these days are nowhere close to hitting the max, but it's peace of mind to have that data.
Here is a link to the script: GitHub
I'm sure you intrepid DBAs will find a way to tweak and make your own. If you have questions about it, hit me up! I love working with stuff like this.