Altering an INT Column to a BIGINT (Dear SQL DBA Episode 11)

You need to change an INT column to a BIGINT in a large table. Learn why this schema change can make your transaction log explode, and how to avoid it.

This is a “listen-able” 21 minute video. Prefer a podcast instead? Find it at littlekendra.com/dearsqldba.

Show notes (transcript with links) are below the video.

Here’s our question for the week

Dear SQL DBA,

I have a 120GB table with 2 billion records and the Clustered Primary Key is an INT column. What’s the best way (and fastest) to convert this INT to a BIG INT?

The database is in the full recovery model, and I can take downtime up to a day on the weekend.

With Concern,

Billions and Billions Served

What happens when you run out of INTs?

Error msg 8115The integer data type in SQL Server allows numbers up to 2.147 billion rows (with a little change).

If you try to insert a row with a value above that, it fails with error 8115:

Msg 8115, Level 16, State 1, Line 142

Arithmetic overflow error converting IDENTITY to data type int.

At that point, you have to do something to insert more rows.

Altering the data type of the column is a “size of data” operation

This seems straightforward at first. Let’s change the column to a bigint!

The first thing you notice is that you’re going to have to remove the Clustered Primary Key. You can’t change a data type with that there.

So we’re already in outage territory (even before you think about whether or not you have foreign keys defined against this PK or replication on the table).

Once you get to the point where you can alter the data, you run into a different problem:

  • Altering the column uses a huge amount of transaction log space
  • It’s reaaaalllll slow

This is because changing from INT to BIGINT is a “size-of-data” operation.

SQL Server has to go through and update Every. Single. Row.

And log it.

And SQL Server has to reserve extra space in the log in case it needs to roll back (because that will require additional logging).

If you get this far without thinking about how much log file you’re going to need and you run out, then things get extra slow, because most of that rollback’s going to be done by just one thread.

You can choose to “reseed” an identity column

The integer data type can support down to -2.147 billion rows (and some change).

You can reset your identity column to -1 and work your way backwards by negative increments.

You can reset your identity all the way to the lowest possible value and work your way back towards zero.

  • The good news: it’s fast.
  • The bad news: I have never met a Change Approver who loves this option. It makes them very itchy and uncomfortable.

Reseeding an identity column like duct tape: it’s a temporary solution, and you’re going to have to fix it for real later. And it’s unfamiliar duct tape.

Who looks at a brand of off brand duct tape and wants to use it to repair their roof?

If you do reseed to get things moving right away, the table just gets bigger, and the job of fixing it gets incrementally harder as time moves on.

Inserting the data into a new table is usually faster than altering the column for large tables, particularly if you can get minimal logging

This is pretty simple if you can take an outage. During the outage:

  • Create a new table using the new data type
  • Set identity_insert on for the new table if you have an identity
  • Insert all the rows with tricks to minimize transaction log use
  • Create all the indexes, constraints, etc to make it identical
  • If it’s an identity column, don’t forget to fix that up so new inserts generate a new identity
  • Use renames or schema transfer to make the new table active

Do test this against a restored backup first so you can make sure you have all the details right and know how much data log and transaction log space you’re going to need. The work with all the indexes can still require substantial log usage.

How to minimize logging

SQL Server can do “minimal logging” in the Simple or Bulk Logged recovery model. NOT in the full recovery model. If your database is in the Full recovery model, you’re going to have to spend some time thinking about recovery models and backups.

With Bulk Logged recovery model:

  • You are going to have a very large and possibly very slow log backup if you do a lot of bulk changes
  • You lose point-in-time recovery for any log backup that contains bulk logged changes

With Simple Recovery model:

  • Changing to this breaks your transaction log backup chain
  • You can restart the transaction log backup chain with either a full or differential backup after the change has done (but again, you’re making a lot of changes so that differential might be pretty slow, depending)

If your database is already in the Simple recovery model, then party, it’s easier. Just make sure you don’t try to get minimal logging while a backup is running, that doesn’t work.

The next step is to read the “Understanding Minimal Logged Operations” in the Data Loading Performance Guide.

If you’re inserting rows into a heap with no nonclustered indexes, typically you can get minimal logging by just using a TABLOCK hint.

If you’re inserting rows into an empty table with a clustered index, you may need additional hints or Trace Flag 610 on your statement.

There’s a handy dandy table in the whitepaper to make it easy.

Typically inserting into a heap with no indexes is the fastest option. If the speed of the change is important, test different inserts.

If you’re using SQL Server 2005, well, first it’s out of support. Second, it doesn’t allow minimal logging for INSERT SELECT operations, you have to do some SELECT FROM OPENROWSET (BULK ) weirdness.

What if you can’t take the long downtime?

Sometimes you just can’t take the outage. In that case, you’ve got to proceed with your own wits, and your own code. This is tricky because changes are occurring to the table.

The solution typically looks like this:

  • Set up a way to track changes to the table – either triggers that duplicate off modifications or Change Data Capture (Enterprise Edition)
  • Create the new table with the new data type, set identity_insert on if needed
  • Insert data into the new table. This is typically done in small batches, so that you don’t overwhelm the log or impact performance too much. You may use a snapshot from the point at which you started tracking changes.
  • Start applying changed data to the new table
  • Make sure you’re cleaning up from the changed data you’re catching and not running out of space
  • Write scripts to compare data between the old and new tables to make sure you’re really in sync (possibly use a snapshot or a restored backup to compare a still point in time)
  • Cut over in a quick downtime at some point using renames, schema transfer, etc. If it’s an identity column, don’t forget to fix that up properly.

Multi-Terabyte example

I’ve worked with cases of Extra Extra Large databases where schema changes were staged outside of production using SAN snapshots and new databases.

If you’ve got an XXL change, SAN magic can be a big help.

Aside: SQL Server 2012 added a new Enterprise feature to eliminate one type of size of data operation

SQL Server 2012 added a feature where adding a new non-nullable column with default values is no longer a size of data operation.

This is an Enterprise Edition feature and it only works for non LOB types (no nvarchar(max), XML, etc). It’s also picky about rowsize.

Remus Rusanu wrote about this feature here in his article Online non-NULL with values column add in SQL Server 2012.

This doesn’t help our case here, because our existing Primary Key isn’t the same as a “default value”. But it might help some readers.

Want more takes on this problem?

Aaron Bertrand has written a very detailed four part series with sample code. Start here to read the first article. (Links to the next three are at the bottom of the post.)

Kenneth Fisher wrote a great post about his experience with altering a column in a 750GB table in his article – Altering a column in a large table: a case study.

If you’d like to know more about how altering columns works internally, check out Remus Rusanu’s article, SQL Server table columns under the hood.

Previous Post
Should I Automate my Windows Updates for SQL Server? (Dear SQL DBA Episode 10)
Next Post
SSMS Tips: Templates and Control+Shift+M

Related Posts

12 Comments. Leave new

What a coincidence, Aaron just created a series about this, you might link to it in the paragraph mentioning Kenneth and Remus.

Reply

Good stuff, thanks Kendra!! Awhile back I wrote a post on an emergency step you can often take, by re-seeding the PK to the negative range of the int, essentially buying yourself another couple billion inserts. This only buys you time however, if you filled it once, you’ll do it again. I’ve gone back and updated the post with a link here for guidance on what you need to do with that time you bought and finally fix your underlying problem. https://sqljudo.wordpress.com/2014/09/16/help-pk-is-running-out-of-identity-values/

Reply

Had recent really good success with this method:

Make couple of schemas, in the db with the big nasty table, “stage” and “old.” Have them both owned by dbo.

Make a blank copy of the table with its clustered index in the stage schema, don’t forget to change the column datatypes that are causing problems.

RDC into the server and fire up Data Tools. I’m assuming that you would be doing this from a high level/DBA role anyway, so you know how to behave in a terminal session on a prod server., 😉

Make a new project, create a data flow. In OLE DB Source, choose SQL Command, Build Query. Add the table and click * (All Columns). Ok the dialog and click Build Query again, and then Ok. You now have all the columns from the table in the command text box with minimal typing. This gives better select performance than SELECT * FROM TABLE or just choosing the table from the dropdown. I read it somewhere in a Microsoft SSIS page a while back but I don’t recall where. It does seem to help in any case.

Add a SQL Server Destination, connect the boxes and open the Destination to do the mappings. Ok your way out, save the project.

Run the project, you can even click inside the Data Flow to see the progress. If you haven’t messed with the MaxInsertCommitSize property, it will do all the commits at the end. You should try a few test runs to see what works best for your situation. In any case, even with full recovery, the log won’t get touched very much, but monitor to see how much growth you get.

When it’s done and stage.bigtable has been validated, move the production table to old.bigtable schema, then move stage.bigtable to dbo, (or whatever your prod schema is). The moves will be almost instant, and the table names and clustered index names won’t conflict.

Yes this requires a downtime, but it runs pretty fast depending on hardware.

I don’t think I left anything out, but it’s also Friday afternoon….

HTH

Reply
manishkumar1980
September 20, 2016 9:46 pm

Hi Kendra,

Here you mentioned please…..
===================================
Multi-Terabyte example

I’ve worked with cases of Extra Extra Large databases where schema changes were staged outside of production using SAN snapshots and new databases.

If you’ve got an XXL change, SAN magic can be a big help.
======================================================
Could you please elaborate this.Please it will be great.

Reply

    Sure. This was a huge database (50TB+) which was being broken up into smaller (but still pretty huge) databases. A lot of the data was historical. Schema was being altered, partitioning was being introduced, lots of changes.

    A SAN snapshot of the production data was presented to another server, so that data could be read from the snapshot and written /staged into new databases. It could also be used for testing. Periodically the production snapshot was refreshed to be able to update with new data, and eventually the staged databases were brought over and swapped into the production server during an outage.

    There’s overhead reading from snapshots, of course, so care had to be taken– but this approach meant not having to use memory and CPU on the production server, not having to do locking in SQL Server when reading the production data, much greater ability to do testing, and a shorter outage when going live.

    Reply

[…] Note: I also talked about size of data operations in a previous Dear SQL DBA episode when I discussed another schema change: altering an INT column to a BIGINT. […]

Reply

[…] Of course, the permament fix is to change the data type from integer to BIGINT, but, if your table is big enough to have this problem, that will also present its own set of challenges – this is a “size of data” operation, meaning  SQL has to modify every single row in the table and the time it takes to do this is directly related to the size of the data in the table. The change will require the table to be locked, lots of transaction log will be generated, etc. See Kendra Little’s post about the issues you will face here. […]

Reply

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Menu