How can I increase a column size for a large, often-used, SQL table and minimize downtime? 1

[AndyInNC]

In MS SQL Server 2014 SP 2, I have a Communication table that is used by many areas of our website to store user notes, comments, and certain email text. The Comments column is defined as nvarchar(4000) and I need to change it to nvarchar(max).

The table has over 250k rows with numerous constraints and indexes. In testing, changing the column size has timed out or filled the transaction log.

At the moment, I have a copy of the data in another table that has the structure change. Could I use sp_rename to swap out the tables and move the data changes a final time?

What is the best way to increase the column size with the least impact to the users?

 

[fredericofonseca]

one possible way if on Enterprise

3 tables required
1 - Table A - current table
2 - table B - old layout, all same indexes and constraints. Partitioned by your primary key with a single partition with data
3 - table C - new layout, all same indexes and constraints. Partitioned by your primary key with a single partition with data

populate table C with all data from A in advance - ensure you have a fast way of updating the data near implementation time.

At implementation time
begin transaction
disable/drop all foreign keys referencing table A
switch table A to table B
Perform updates from table B to table C
alter table A (which will be empty at this point) and change datatype
switch table C to table A
enable/recreate all foreign keys referencing table A
commit
On the above the foreign keys is what takes time - switch operations are metadata only and take no time at all

Another possible way (works on standard also)
add new column to table (comments_new varchar(max) null)
in small batches update this column with the contents of the current field - this can be done over a period of time.

at implementation time
begin transaction
drop any indexes with comments
sp_rename comments to comments_old
sp_rename comments_new to comments
do final updates of comments_new from comments_old
alter table drop column comments_old
recreate any required indexes (these can be create in advance)
commit






Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886