Transaction log is full changing column type in a large table

I've got a table of more than 400 million rows and want to convert the datatype of one of its columns, specifically datetime to datetime2(2).

If I execute my statement, I get the following error:


The transaction log for database 'xxxx' is full due to 'ACTIVE_TRANSACTION'

So is here any possibility to update this table?

(My log file can have a maximum size of 150GB, I don't have any more free space available.)

Scott's answer made me realise you might have enough space for this:

Create a datetime2 column with a temporary name, and transfer the original column contents to it in batches (to prevent your log running out of space -- and I'm assuming your database is in Simple recovery model).

Then drop the original column, and rename the new column to the old column name.

StackExchange Database Administrators Q#163313, answer score: 5