Post reply

Transaction log full error while altering a column

  • July 17, 2015 at 7:46 am

    #309948

    I am altering a table ( changing the data type to varchar (8000) from nvarchar (1500) ) with 352929 rows. I get the transaction log full error.

    The database is in FULL RECOVERY model. I changed the recovery model to SIMPLE and performed the alter but I still run into error.

  • July 17, 2015 at 7:59 am

    #1813291

    You don't have enough space in the log for the ALTER. It's a single transaction, so recovery model doesn't apply (and please make sure you fix the broken log chain you've caused)

    If you can't make the log large enough to do this in a single transaction, then add a new column, copy the data to the new column in batches, drop old column, rename new one.

    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

  • July 17, 2015 at 8:11 am

    #1813295

    GilaMonster (7/17/2015)

    You don't have enough space in the log for the ALTER. It's a single transaction, so recovery model doesn't apply (and please make sure you fix the broken log chain you've caused)

    If you can't make the log large enough to do this in a single transaction, then add a new column, copy the data to the new column in batches, drop old column, rename new one.

    Thank you for the solution .

    I am thinking of trying this as well before I add a new column....

    Resize the log file to 2GB then perform the alter and then shrink the log file. Do you think it's worth a shot?

  • July 17, 2015 at 1:58 pm

    #1813428

    Depends on how much data is in that column. It's a fully logged operation, so you need log space > size of data being affected (in this case the varchar(8000)'s contents. If there are no more than 1500 characters, then my rough maths suggests 1GB may be enough (providing there are no concurrent changes), but YMMV.

    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

Viewing 4 posts - 1 through 3 (of 3 total)

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