Alter field in table with 135,000,000 records

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
thayduckProgrammer AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott PletcherSenior DBACommented:
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.
0
Anthony PerkinsCommented:
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.
0
Scott PletcherSenior DBACommented:
>> 1. Add a new column <<

That will take at least as long, and probably longer, than converting the existing column.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Anthony PerkinsCommented:
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.
0
Scott PletcherSenior DBACommented:
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.
0
Anthony PerkinsCommented:
Ah, I did not know that.  Thanks.
0
thayduckProgrammer AnalystAuthor Commented:
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.
0
thayduckProgrammer AnalystAuthor Commented:
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]
      ,[Rdesc]
      ,[Comp]
      ,[Compd]
      ,[Init]
      ,[Unit]
      ,[Orders]
      ,[Open_date]
      ,[Clos_date]
      ,[Make]
      ,[Model]
      ,[Year]
      ,[City]
      ,[Prov]
      ,[Status]
      ,[Notes]
      ,[Vendor]
      ,[Vend_name]
      ,[Meter_reading]
      ,[Meter_type]
      ,[Budget]
      ,cast([RT_code] as char(3)) as RT_code
      ,[Repair Type]
      ,[Clab]
      ,[Cpart]
      ,[Cshop]
      ,[Hours]
      ,[Labour]
      ,[Parts]
      ,[Rec]
      ,[YTDmtce]
      ,[YTDacc]
      ,[YTDdamg]
      ,[YTDabn]
      ,[YTDschd]
      ,[YTDroad]
      ,[YTDdown]
      ,[YTDrepqty]
      ,[YTDcomm]
      ,[YTDmisc]
      ,[YTDpart]
      ,[YTDlab]
      ,[YTDlabhrs]
      ,[YTDrec]
      ,[YTDfuel]
      ,[YTDutil]
      ,[YTDutil2]
      ,[Batch]
      ,[LogDate]
      ,[UserPin]
  into [dbo].[tableb]
  FROM [dbo].[tablea]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.