Alter field in table with 135,000,000 records

Posted on 2014-08-19
Last Modified: 2014-08-29
I am trying to alter a field in tableA  from 2 char to 3 char, in a table with over 135,000,000 records.

When running script below, it runs for 35-40 minutes and stops saying no more room or just disconnects my job.
I assume it stops because of all the records it has to alter.
I have no control over servers. Just wondering if there is a better way.

alter table tablea alter column [rt_code] char(3)

The next thing I will try is:
copying tablea to tablea_bk
deleting all records in tablea
then alter tablea
then copy records back to tablea from tablea_bk
Question by:thayduck
    LVL 68

    Expert Comment

    Yeah, there's no easy way to do that.  You'll have some delays and/or down time for that table no matter what you do.

    The single biggest thing you can do to speed up the entire process is to make sure you have sufficient pre-allocated, and thus pre-formatted, log space to handle the biggest transaction.  [I had a 45 minute task go down to ~30 secs just from doing that.]  I'm assuming you have IFI on, so that data file growth does not have the same issue.

    Naturally tablea_bk should define the column as char(3) already.  If you're already familiar with Change Tracking, you could use it to re-sync the tables, but it's not trivial.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    1. Add a new column
    2. Copy the data from the old column in batches.
    3. Drop the old column
    4. Rename the new column with the old name.
    5. Fix all the Foreign Keys, indexes if necessary.
    LVL 68

    Expert Comment

    >> 1. Add a new column <<

    That will take at least as long, and probably longer, than converting the existing column.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    That will take at least as long, and probably longer, than converting the existing column.
    Are you sure? I thought it (ALTER TABLE ADD ColumnName ...) would be just a metadata change and therefore practically instantaneous.
    LVL 68

    Expert Comment

    Adding a fixed-length column will have to change the structure of all rows, just like increasing the length of a fixed-length column does.
    LVL 75

    Expert Comment

    by:Anthony Perkins
    Ah, I did not know that.  Thanks.

    Author Comment

    I have a DBA looking at this issue.
    He is trying some things in SQL Sand Box.

    I think he will have to do a:

    Copy A to BKA
    Truncate A
    Alter A
    Copy BKA back to A

    It's just the amount of records that is causing this issue.

    Accepted Solution

    DBA came up with this,  then deleted tablea, then renamed tableb to tablea.
    This only took around 5 mintutes to complete.

    Per DBA:
    From the Activity Monitor, it seems most of wait time is on PAGEIOLATCH_EX.  So we are waiting on disk IO most of the time.

    So the important element is that SELECT .. INTO new_table FROM source_table does very little logging.  In this select statement, I cast the column we want to change to the new format.  I do some renames of the tables and we are done.

    SELECT [Orgn]
          ,cast([RT_code] as char(3)) as RT_code
          ,[Repair Type]
      into [dbo].[tableb]
      FROM [dbo].[tablea]

    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    728 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now