Altering a big table

Question

Altering a big table

Sander A.

Default port

Points: 1440
More actions
November 8, 2009 at 4:03 am

#213435

I'm trying to alter a table with 350.000.000 records. There is one float column that i want to change to decimal(6,2). When i run the alter statement the alter fails after an hour with the folowing message:
The statement has been terminated.
Msg 9002, Level 17, State 4, Line 2
The transaction log for database '*****' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
So i looked into the sys.database and there i found the that log_reuse_wait_desc = ACTIVE_TRANSACTION.
The logging is already simple, and there is about 400gyg of free space on the log-drive. But this 400gyg isn't enough to do the alter statement. What should i do to get this alter statement to work??

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Try create a new column, batch-update the values, then drop the old column. Might lessen the log impact. Run checkpoints between each (since you're in simple) to mark the log as reusable.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

altering float to decimal(n,m) will cause your full table to be rewritten !

Best you can do to take control is - as Gail advised - create a new column (decimal) and then copy the data in batches.

If you can, create the column nullable. This will minimize the catalog impact.

Take checkpoint after every batch to "free" the log file(s).

Next time, take a bit more time to design column, so you know they are being created using the correct datatype and size.

Size does matter !

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Steve Jones - SSC Editor SSC Guru Points: 736297 More actions · Answer 3

I think that Gail's idea is the best. Note that I believe the space will not be reclaimed in the table. I thought I'd read that once you drop an interior column, the space is still used in the actual table.

The reason you are having issues is that the entire table being rewritten is one large operation. Simple mode truncates out committed transactions every minute or so, but in this case it can't clear the log of the one large transaction. You need more log space, or need to follow Gail's advice.

Note if you get more log space, I'd grow the log to 600GB-1TB first, to prevent log growths.

Sander A. Default port Points: 1440 More actions · Answer 4

GilaMonster (11/8/2009)
Try create a new column, batch-update the values, then drop the old column. Might lessen the log impact. Run checkpoints between each (since you're in simple) to mark the log as reusable.

thanx, this is indeed the best option.

I DO know altering a table like this involves a complete rewrite, and simply suggesting I should think more carefully about designing my tables is easy to say: The table already existed before i started working here, so i'm just cleaning up the mess. Nowadays we do think twice about every column we create.

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 5

Sander A. (11/9/2009)
.. simply suggesting I should think more carefully about designing my tables is easy to say: The table already existed before i started working here, so i'm just cleaning up the mess. Nowadays we do think twice about every column we create.

Hey, hey ... no offence meant !

Most of us have our feet in the dirt on a daily basis and even when trying to implement best practices, get into deep [bleep] every once in a while. 😉

As with all projects: the bigger they get, the bigger impact small issues will have !