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.
DA2in batches? See this answer for a related question