1

I am attempting to alter a column on a specific table; however the process is failing because the log file runs out of space once the disk runs out of space.

The size of the table is less than the amount of free disk space.

Can anyone tell me what I'm doing wrong?

Scenario :

  1. Alter column size from numeric(9,10) to numeric(18,6)

Statistics :

  1. Table size - 40 GB
  2. Table rows - 40 million
  3. Disk free space where this table located - 50 GB
  4. Database Recovery model : Full
  5. Mirroring is configured
  6. SQL Server 2012 Enterprise

The error I'm receiving is:

Alter column operation is failed with an error "The statement has been terminated. The transaction log for database 'x' is full due to 'LOG_BACKUP'

  • Can you mention the error. what you are find out after alter the column size. – Md Haidar Ali Khan Mar 2 '15 at 12:12
  • Alter column operation is failed with an error "The statement has been terminated. The transaction log for database 'x' is full due to 'LOG_BACKUP' – Paresh Mar 2 '15 at 13:02
4

Numeric(9, 10) is not a valid data type specification. The scale must be less than or equal to the precision. I'll assume the actual column has a smaller scale specification.

When you change a column from a precision of 1-9 to one of 10-19, the storage size increases from 5 to 9 bytes. This is a fully logged operation regardless of your recovery model and logging of DDL operations is expensive as you have found.

One method is to add a new NULL column, and then update the new column value from the old one in batches in order to reduce log space requirements and allow updates to be applied to the mirror. Once all rows are updated, drop the old column and rename the old to the new. It is best to update in ranges of your clustered index key. If you need help with the batching of updates, post your existing table structure.

Keep in mind that mirroring might not be able to keep up during the operation (increasing log space needs) even if you backup the log during the update. You should do this during a maintenance window anyway so you have the option of removing the mirror during the maintenance and reestablishing afterward.

Another method is to create a new table with only the clustered index, load with INSERT...SELECT, drop the old table and rename the new table to the old name, and recreate constraints and indexes. This would allow you to minimize logging by removing the mirror and changing the recovery model to SIMPLE or BULK_LOGGED during the maintenance.

|improve this answer|||||
  • Yes, it's (10,2). We have already think about this step to add new column and updates with old column values in small batches. Due to this table used in replication, it apply those 40 millions updates at subscriber sides too and degrade replication performance. "INSERT...SELECT, drop the old table and rename the new table to the old name"- this steps need another 40 GB free disk space for new table same as original table size, which we don't have. – Paresh Mar 2 '15 at 12:59
  • You mentioned you have 50GB free disk space, so I would expect that might be enough to temporarily have old an new tables side-by-side. To address the replication latency, you could pause between batches. It is best to plan to maintain free space of at least 120% your largest table as necessary overhead for maintenance. – Dan Guzman Mar 2 '15 at 13:06

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.