1

short version: what options do i have to move a table that has blob data over to a new filegroup, without blowing out the txlog?

details: we have an extremely over-allocated filegroup ("DA1"). the plan is: move all the objects to a new (right-sized) filegroup("DA2"), shrink/delete DA1 to reclaim all that disk space*. Easy, right? So, I already moved most all the objects to DA2, most all were pretty small. This last table has blob data, it's about 250GB. prod is in FULL recovery. What we've tried in dev: 1) tried a shrink, took over 11 hours before we killed it. 2) i tried SELECT INTO a new table on DA2 so I could delete&rename, however, the log grew too large and consumed the log drive (constraints here too :( I do have large maintenance windows on the weekends, but cannot change the recovery model. Any other ideas I can test out?

*we're talking about 2TB to reclaim, and thus has created problems/pressure on restoring this db to lower environments because it's just_too_big.

  • If the extra space is all whitespace, why would that be causing your restores to take longer? Are you using IFI? – George.Palacios May 2 at 15:22
  • 1
    Can you copy rows to the new table in DA2 in batches? See this answer for a related question – Scott Hodgin May 2 at 15:23
  • @ScottHodgin I will try that too, but txlog bkp every 15mins (id have to ensure txlog doesn't get bigger than this during the batches.) looking for other creative ideas. I was also thinking of, during a maintenance window, pulling the data into a new, local database. Then performing the DA1 work, then re-inserting (in batches) the table into DA2. – paul May 2 at 15:47
  • You could back up the log more frequently during this maintenance to keep the size in check. You could also consider copying the data to a table in a new database, in simple recovery, and just checkpoint after every batch. Then copy everything but the LOB data to a new table in the original database on the new filegroup, drop the original table, and rename the new after you copy the LOB data back to the new table (and you can do this in a way that takes as long as it takes to minimize impact; you can always use views to make the LOB data look local for the rows that haven't been copied yet). – Aaron Bertrand May 2 at 16:00
  • @paul sorry, I wrote all of that before reading your comment. :-\ Anyway I've blogged about something along the same lines but a little simpler here. – Aaron Bertrand May 2 at 16:04
0

You can try moving the data by rebuilding indexes. There are some limitations to this, for example, your clustered index rebuild will have to be done OFFLINE (a limitation of ONLINE rebuilds) and any non-clustered indexes can be rebuilt online as long as the LOB data is not part of the index key or as an include column.

Also, during the index build operation, the transaction log can be backed up and truncated, provided you do not run the index operation in an explicit transaction.

Microsoft has a guide on calculating the space required for here. They also have some general recommendations such as switching to BULK-LOGGED recovery to reduce the amount of transaction log data. See here for guidelines from Microsoft.

One of the advantages to this is you can move an index at a time, so you can do this over multiple maintenance windows if need be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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