Add column taking too long

I'm trying to add a column to a database. The query has been running for 25mins and it's locking web access to the table and breaking our website:

alter table MyTable
add MyNewColumn varchar(max) not null
default ('')

The table contains binary data in a different column and is quite large.

Will cancelling by using the red "Cancel Executing Query" button cause additional problems? I'm just trying to figure out if I should attempt to cancel the query at this point, and what will happen since it's been running for so long.

Several problems with your approach:

• adding a not null MAX column with default value. Make the column nullable and don't specify a default. Adding a default '' instead of letting it be nullable is incorrect semantically and a waste of significant space per-row. Make you code handle NULLs correctly.

• adding a not null with default column to a table without any testing/preparation. This is a size-of-data operation as every single row in the table has to be updated. Has the potential to generate a huge amount of log because is as a single transaction. With a MAX type not even online column add in SQL Server 2012 could help, but again there is little point on having a MAX column not null with default ''.

• even if the non nullable MAX with default is truly required, preparing for such an operation would require a mandatory evaluation of the log space needed (between 1.5 and 2.5 times the size of data) and pre-growth of the log file. Most likely, this is what most of the time is spent right now on, growing and zeroing the log.

• doing it live.

• asking for real-time advice on forums...

If you attempt to cancel the query you may or may not succeed, and the cancellation can be fast or can itself take another 25 minutes or more, depending on where is the current progress at (could be blocked on locks or could be progressing). In the later case resorting to desperation and attempting to restart the server will cause the DB recovery to last even longer.

StackExchange Database Administrators Q#17030, answer score: 7