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

Question

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,

Answer 1

This case happened in our UAT environment. After we restore the database again from the latest backup, this problem doesn't happen again. The alter is completed within mili-seconds.

In my opinion, there're certain issue since the last restoration.

Many thanks all,