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:
Alter Table
ALTER TABLE Adress ALTER COLUMN Coordinate decimal(18, 3) NULL
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...
- Uncheck
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
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