How can I add a rowversion column to a large table with minimal downtime

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.

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.