Skip to main content
Database Administrators

Return to Question

Added the generic tag of the database management system of relevance.
Link
MDCCL
MDCCL
  • 8.5k
  • 3
  • 31
  • 62
added 20 characters in body
Source Link
Cyndi Baker
Cyndi Baker
  • 685
  • 7
  • 18

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records. It is very hot. Without developing a full reproduction environment of this level of activity, how can I am asking:ensure a statement like

ALTER TABLE x DROP COLUMN y

will TABLOCKX in my ALTER TABLE x DROP COLUMN y allow this action tosuccessfully obtain an exclusive lock the table, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)? Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes: Later I will re-index ONLINE to defragment the index and reclaim the space.

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records. It is very hot. Without developing a full reproduction environment of this level of activity, I am asking: will TABLOCKX in my ALTER TABLE x DROP COLUMN y allow this action to lock the table, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)? Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes: Later I will re-index ONLINE to defragment the index and reclaim the space.

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records. It is very hot. Without developing a full reproduction environment of this level of activity, how can I ensure a statement like

ALTER TABLE x DROP COLUMN y

will successfully obtain an exclusive lock, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)? Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes: Later I will re-index ONLINE to defragment the index and reclaim the space.

Source Link
Cyndi Baker
Cyndi Baker
  • 685
  • 7
  • 18

How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change.

The table has two different services constantly inserting and updating records. It is very hot. Without developing a full reproduction environment of this level of activity, I am asking: will TABLOCKX in my ALTER TABLE x DROP COLUMN y allow this action to lock the table, do the drop quickly, and then unlock the table?

How will SQL Server queue this request (eg First In First Out)? Can I be sure the DROP COLUMN will truly only take a few moments?

Locking this table in PROD for an extended time will be unacceptable so I am trying to avoid surprises.

Other notes: Later I will re-index ONLINE to defragment the index and reclaim the space.