5

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.)

  • Do you have more space on any other drive letters, network drives, or external drives that you can use temporarily? If so, we can discuss ways you can use that space for the convert operation. Also, is this a production server, or are you free to take databases offline etc.? – T.H. Feb 6 '17 at 10:57
  • If the database is in in the SIMPLE or BULK_LOGGED recovery model, you could create a new table using a minimally logged SELECT INTO, drop the old table, rename, and create indexes/constraints afterward. Similarly, you could create a new table and using a minimally logged INSERT...SELECT. – Dan Guzman Feb 6 '17 at 13:09
3

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.

1

I would recommend using SSIS.

SSIS Package

Control Flow: -> Load data into flat file

Load data into flat file

-> truncate table / change data type -> Convert datatype into datetime2 -> upload data into table (batch mode)

Convert datatype into datetime2

0

One option 'might' be to BCP export the current data (while converting the column in question to its new data type) to a flat file, drop and recreate the table with the new data type and BCP import the data using the -b parameter to limit the number of rows logged in each batch, thus preventing your transaction log from running out of space.

0

This subject is covered in great depth by Kendra Little at this link.

The problem is that as you go down the table you're triggering continuous page splits on top of the reorganisation of the table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.