Adding Nullable Column To Production DB taking too much time

Question

I have running production DB which have normally 50-100 insert per minute. I am trying to add a new column in the production DB using,

ALTER TABLE MyDB ADD [MyColumn] varchar(30)
GO

This query keeps running and not finishing until I have stopped it. What else I can do?

Answer 1

Adding a nullable column like that is generally a meta-data-only operation. That is it will complete nearly instantly as long as it can get a schema stability lock on the table for the short time it takes to add the column.

If you're seeing it take a long time, the cause is most likely that other sessions are blocking the ALTER TABLE statement. Realize, too, that when you run the ALTER TABLE statement, while it's waiting to get a schema stability lock, it will block subsequent queries on that table. You probably want to look at setting the lock timeout for your session prior to running the ALTER TABLE statement.

If blocking other sessions is problematic, you could wait until there is a period of lower activity, and run the command then. Alternately, you could put the database into single user mode, alter the table, then put it back into multi-user mode - of course that would prevent any sessions from accessing the database during that time.

To put the database into single user mode and alter the table, you'd do something similar to this:

USE master;

ALTER DATABASE [MyDB] 
SET SINGLE_USER 
WITH ROLLBACK IMMEDIATE;
GO

USE [MyDB];
ALTER TABLE [MyDB] ADD [MyColumn] varchar(30) NULL;
GO

USE [master];
ALTER DATABASE [MyDB]
SET MULTI_USER;
GO

Be aware though, setting the database into single user mode like that will cause any in-flight transactions to be rolled back, which, depending on how your client software is designed, might cause a loss of data. Any transactions in flight at the time of the ALTER DATABASE statement would need to be re-executed once the database is brought back into multi user mode. Take a look at the Microsoft Docs page for ALTER DATABASE, and in particular the section on WITH ROLLBACK, for various options that control how the server puts the database into single user mode.