Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Using SQL Server 2008 and later, I want to add a rowversion column to a large table however when I simply

ALTER TABLE [Tablename]
ADD Rowversion [Rowversion] NOT NULL

Then the table is unavailable for updates for too long.

What strategies can I use to reduce this downtime? I'll consider anything. The simpler the better of course, but I'll consider any strategy.

My thinking is that as a last resort, I could maintain a copy staging table maintained by triggers and then sp_rename the staging table into the original table. But I'm hoping for something simpler/easier.

share|improve this question

migrated from stackoverflow.com Mar 21 '12 at 14:52

This question came from our site for professional and enthusiast programmers.

up vote 22 down vote accepted

Consider creating a new table with the same schema plus the rowversion column, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the underlying tables & views.

Inserts should be sent to the new table, updates should move data to the new table, and deletes should be applied to both tables.

Then do batch moves in the background, moving as many records at a time as you can over to the new table. You can still have concurrency issues while this is going on, and some craptacular execution plans, but it lets you stay online while the moves are happening.

Ideally, you start the process on a Friday afternoon to minimize the effect on end users, and try to get it done before Monday morning. Once it's in place, you can change the view to point to just the new table, and the craptacular execution plans go away. Ideally.

To avoid the triggers firing when the data is being migrated in batches, look at the number of rows in the deleted/inserted tables in the trigger, and skip the activities if they're near to the number of rows in your batch.


In the finish, Michael decided to skip the view (and not delete from the original table) to get more stable plans. The trade off was holding essentially two copies of the table. He turned it into a series of blog posts.

share|improve this answer

If you have time to plan ahead, there's a much easier solution... (usually)

The long locks are almost certainly caused by page splits at the storage layer. So force them on your own schedule.

  1. Add a NULL-able temporary column with datatype VARBINARY(8).
  2. Find available slack time in the database to update batches of the existing records with a valid value for the field. (0x0000000027F95A5B for example)
  3. The updates will force the necessary page splits and allocate more space to the table.
  4. When you're caught up, drop the temporary column (doesn't touch the allocated storage) and add the rowversion column.
  5. No page splits, and a lock that's needed only long enough to populate the values.

I've used this successfully to add a rowversion column to a 150M row table in under 10 minutes.

Caveat... if you have a table with large varchar fields (especially varchar(max)) SQL Server decides to rebuild the table instead of re-using the newly available space. Still trying to figure out a way around that one.

share|improve this answer
    
Interesting, I guess I didn't specify what "too long" meant in my question. If >30 minutes is too long for your scenario and 10 minutes is tolerable, this solution would work. My scenario involved trying to achieve zero downtime or more specifically < 10 seconds which is achieved by Brent's answer. – Michael J Swart Jan 29 '15 at 14:19

If the TIMESTAMP you are adding is NULLABLE:

  1. Add a VARBINARY(8) column
  2. Populate with data.

After it is populated, in back to back SQL statements, DROP the VARBINARY(8) column you just added and populated, and add the TIMESTAMP NULL column.


If the TIMESTAMP you are adding is NOT NULLABLE:

  1. Add a BINARY(8) column
  2. Populate with data.

After it is populated, in back to back SQL statements, DROP the BINARY(8) column you just added and populated and ADD THE TIMESTAMP NOT NULL column.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.