Sql-server – move blob data to new filegroup

sql serversql-server-2008

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.

Best Answer

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.

Related Solutions

Sql-server – Moving Clustered Index to new filegroup results in excessive filegrowth

I believe I've found my own answer to this question. When setting SORT_IN_TEMPDB on, the filegroup no longer expands unpredictably, and a shrink with truncate only is successful in recovering space which is all trailing space. If trying this, note that this could result in additional space requirements for TempDB.

Sql-server – Database Migration – Create New Filegroup and Move Objects

This can be done if you have clustered indexes (either as the primary key constraint or on their own) on the tables that you wish to move.

So restore the database, add the new filegroup and then...

For primary keys:-

 ALTER TABLE [SCHEMA].[TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME]

 ALTER TABLE [SCHEMA].[TABLE NAME] ADD PRIMARY KEY CLUSTERED 
 (
   [Column]  ASC
 )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
 GO

For clustered indexes:-

CREATE CLUSTERED INDEX [CONSTRAINT NAME] ON [SCHEMA].[TABLE NAME]
(
    [Column] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = ON, 
    ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [NEW FILEGROUP]
GO

Please be warned that this may take a while to do on a VLDB!

Best Answer

Related Solutions

Sql-server – Moving Clustered Index to new filegroup results in excessive filegrowth

Sql-server – Database Migration – Create New Filegroup and Move Objects

Related Question