Sql-server – Alter column opreation failed due to disk space in SQL Server

alter-tabledisk-spacesql servertransaction-log

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'

Best Answer

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.

Related Question