'SQL Server 2012 takes long time on simple alter to add NULL columns

I have a problem to alter table in SQL Server 2012, it takes a long time to add 04 columns being allowed NULL to large table with 340 columns and approximately 166M rows and 01 non-clustered index

This problem happens only specific table after restoring. I'm waiting the execution for 10 hours but it's not finished so I must cancel it for more investigation. It's such strange because the script is really really simple as below, and we have done it successfully before:

alter table sample_database.sample_schema.sample_table
add column_001 int null
   ,column_002 numeric(18,4) null
   ,column_003 nvarchar(500) null
   ,column_004 int null;

My questions are:

  • Why does it happen strangely?
  • How to solve this because it's related to our deployment package? We have done the workaround as creating new table with new columns and load data. But it doesn't work to us.
  • How to prevent this problem in the future?

Many thanks all,

sqlsql-servertsqlsql-server-2012alter-table


Solution 1:[1]

If it is 2012 (according to tag), this may happen:

http://rusanu.com/2012/02/16/adding-a-nullable-column-can-update-the-entire-table/

If adding a nullable column in SQL Server 2012 has the potential of increasing the row size over the 8060 size then the ALTER performs an offline size-of-data update to every row of the table to ensure it fits in the page. This behavior is new in SQL Server 2012.

Solution 2:[2]

The reason behind this is explained by @Anton. -Page Split

And for workaround you could follow these steps:

  1. Create clone table with empty rows(e.g. SELECT * INTO <New table> from sample_table WHERE 0=1)
  2. Add new columns in .
  3. Copy data from sample_table to .
  4. Once step 3 completes modify the sample_table name and rename to sample_table.

You should also try to add columns with NOT NULL values and once columns added then modify it accordingly.

Solution 3:[3]

I had this same issue and a simple server restart worked.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Anton
Solution 2 Mr. K
Solution 3 Sambarilove

Related Questions