Change Datatype of Column in SQL Server

2
votes

We have a SQL Server 2008 database with a table containing more than 1.4 billion records. Due to adjustments of the coordinate system, we have to expand the datatype of the coordinate column from decimal(18, 2) to decimal(18, 3).

We've tried multiple things but everything resulted in an exception (transactionlog is full) after about 14 hours of execution.

These are the things we tried:

  1. Alter Table

    ALTER TABLE Adress
ALTER COLUMN Coordinate decimal(18, 3) NULL

  2. Designer

    • Uncheck Tools > Options > Designer > Prevent saving changes that require table re-creation
    • Open Designer
    • Change datatype of column to decimal(18, 3)
    • Right-click > Generate Change Script...

What this script does, is creating a new table with the new datatype, copying the old data to the new table, drop the old table and rename the new table.

Unfortunately both attempts result in a transaction log full exception after 14 hours of execution.

I thought, that changing the datatype via ALTER TABLE... ALTER COLUMN... is only changing the metadata and should be finished in the matter of (milli)seconds?

  • Do you know of any other method I could try?
  • Why are my attempts (especially #1) needing that much time?

Thanks in advance

4
sql-serverdatabasesql-server-2008tsql
Some changes to tables can be performed as metadata only changes. By no means is it all changes, and the one you're performing does require updating every row. So, I guess you need more log space. – Damien_The_Unbeliever
Try select CONVERT(binary(8),CONVERT(decimal(18,3),1.5)),CONVERT(binary(8),CONVERT(decimal(18,2),1.5)) and you'll see that the representation is quite different. – Damien_The_Unbeliever
A. How big is the used space used by the table (Disk usage reports)? B. And how much disk space you have for your experiment? If B > A, than you can consider creating new table from scratch and bcping data to it (whic h can be done in batches and so minimizing the transaction log usage). As a side effect benefit you'll get more compacted and less fragmented organization of data. You should also use compression if not used yet – Jan
A full transaction log is a full transaction log. This isn't related to the ALTER TABLE command. The log isn't a quirk either, it's what allows you to cancel the change or handle any failures. Without the log, cancelling the query would leave you with a bad database. Backup the log to release any active records and add more space. You could also try adding a new log file stored on a different disk – Panagiotis Kanavos
The full transaction log is definitely related to the ALTER TABLE. The data modification is being recorded in the transaction log. – Sean Pearce

4 Answers

4
votes

Well the main issue seems large amount of data saved into the table. Your both attempts also seem fine. They both will definitely take time I must say as the data is large.

Each time you alter a column data type the SQL SERVER tries to convert existing data into targeted data type. Processing the conversion on large amount of data may cause delay in execution.

Moreover I wonder if you have any trigger on the table.?

Well! Finally I would suggest you following steps. Give it a try at least

  1. Remove any primary keys/indexes/constraints pointing to the old column, and disable any trigger (if there is any).
  2. Introduce a new nullable column with the new data type (even if it is meant to be NOT NULL) to the table.
  3. Now make an update query on the table which will set the new column value to the old column value. You can do updating in chunks while updating 1000/100000 batches of the records. And also you can apply conditions to the query for better results.
  4. Once you update all the table by setting new column values to old column then remove the NULL character to NOT NULL from designer (if it is meant to be NOT NULL).
  5. Drop/Delete the old column. Perform Select Query and Verify Your Changes.

Last Point I should add is your database transaction log is also full which can be shrunk but with some precautions. Here is very good example how to reset your transaction log. Should take a look at this too.

Hope This Helps. :)

1
votes

The solution is to do the updates in batches, easing the pressure on the log file.

Method 1:
a) Create a new table with the new definition.
b) Copy the data to the new table in batches.
c) Drop the old table.
d) Rename the new table.

Method 2:
a) Create a new column with the correct definition.
b) Update the new column with data from the old column in batches.
c) Drop the old column.
d) Rename the new column.

Method 3:
a) BCP the data into a file.
b) Truncate the table.
c) Alter the column.
d) Set the recovery model to bulk logged or simple.
e) BCP the data from the file into the table.
f) Set the recovery model back to full.

0
votes

Add new column as the last column
If you try to insert before the last column it could take a long time
NewCoordinate decimal(18, 3) NULL 

select 1 
while(@@rowcount > 0) 
BEGIN
    UPDATE TOP(10000) Adress 
    SET   NewCoordinate  = Coordinate
    WHERE NewCoordinate <> Coordinate
END
0
votes

That is my suggestion: ADD a field to your table and name it like below:

NewCoordinate DECIMAL(18, 3) NULL

WHILE(1 = 1)
BEGIN
    UPDATE TOP(1000) Adress SET NewCoordinate = Coordinate
    WHERE NewCoordinate IS NULL
      IF (@@ROWCOUNT < 1000)    
    BREAK    
END

Try to keep your transaction like small.

And Finaly drop your Coordinate field.