Database Locking

[DaveR]

Hi,

I am trying to add a new column in the enterprise manger to a table.
The table contains a very large number of rows - when I try to add the column the entire database gets locked.

What is the best way to temporarily remove all locks from a table?

Thanks

[szlamany]

Hi,

I am trying to add a new column in the enterprise manger to a table.
The table contains a very large number of rows - when I try to add the column the entire database gets locked.

What is the best way to temporarily remove all locks from a table?

Thanks

You really should not add a column with EM while users are in the DB - especially a large table.

We did this once with a table with 3+ million rows - it took 30 minutes to add the column.

EM copies the table to temp space - drops the table - creates a new table - all very ugly stuff.

If you are adding the column at the end of the table, use QUERY ANALYZER to ALTER TABLE - do not use EM if you can avoid it!

[Blade]

If you add the column on with no defaults and allow nulls then SQL Server will add it instantly without the use of a temp table.
We do it this way and then update the table in chunks afterwards if we needed to have the column populated for existing data.