Moving all Heap Tables to another Filegroup in SQL Server

Question

In SQL Server 2017, I want to move ALL Heaps in a DB to a secondary filegroup. What's the easiest way to do this?

This question is only for Nonclustered Indexes.

In the process of optimizing an older legacy database.

I want to write a script to move all heap tables with a cursor loop similar to the Stack Overflow link above.

its a legacy database, around 500 GB, looking for Ola hallengren type script — MattSmith, Jun 7 at 0:17

Danilo Braga · Accepted Answer · 2019-06-05 21:37:57Z

3

As far as I know, you have two options:

Physically move the data. Create a new empty heap in the secondary filegroup / Copy the data / rename the object, everything inside of implicit transaction. And don't forget to script out indexes, constraints etc.
Create a clustered index in the secondary filegroup, and then drop it.

I'd prefer the first option.

answered Jun 5 at 21:37

Danilo Braga

1,0461 gold badge5 silver badges18 bronze badges

I don't like copying data between tables in a database in this type of manner because it leaves the door open for connections to come in and manipulate/read things in an inconsistent state. I am upvoting your answer, but only in support of the Clustered Index create/drop approach as that will appropriate lock/block the table and insure data consistency. Obviously this would need to be performed during a maintenance window, but this insures you don't have any possibility for data in an inconsistent state. – John Eisbrener Jun 7 at 13:40

add a comment |

Vinod Narwal · Accepted Answer · 2019-06-05 23:25:35Z

Below solutions can be helpful to achieve the goal:-

Create a clustered index on the table and mention to move it on secondary filegroup. see below link for reference. https://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/
Create another table with the same columns in the secondary filegroup. Copy data from one table to another table in chunks to optimize performance. This will copy data into secondary file allocation units. Then Drop the first table and rename the secondary table. There could be steps if the table is having a dependency.

2 Answers 2