How to Move Table to Another Filegroup?
Suppose we had a database that has grown very large in size and located on the D drive that had a 100 GB Capacity. Now drive D is running out of space. We had other drives on the server where there was available disk space, so our immediate solutions will be to move large tables from D drive to different drive where there is enough space to make some room for your database. To move table to another filegroup, we need to perform below steps.
- Identify the Large tables that we will be moving to another filegroup placed on drive E.
- Create new filegroup on disk E and if you have already a filegroup in this drive you can use that one.
- Add a data file to this newly created filegroup.
- Drop the clustered index from the table that has been identified for the movement and recreate it to the another filegroup.
- Create cluster index on the same table.
Move a table to Another Filegroup
We can move table from one filegroup to another filegroup through dropping and recreating the clustered indexes. Below T-SQL codes that will move your complete table from filegroup1 to filegroup2. Run below ALTER TABLE statement to move a table named TAB1 that has a cluster index named PK_TAB1 to another filegroup TEST_DATA_2.
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO TEST_DATA_2) GO ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID) GO
where TAB1 is table name PK_TAB1 is clustered index key and TEST_DATA_2 is target filegroup. It’s not necessary to create the another filegroup in to separate disk. This method will be useful if you want to move a table from one filegroup to another filegroup irrespective of the filegroup drives.
If your table doesn’t have a clustering index by any reason, you can still move table by creating a clustering index and then follow the same process to move it.
- Understanding SQL Server Indexes and difference between Clustered and Nonclustered Indexes
- Rebuild Index vs Reorganize Index Operations
- Understanding Fill Factor and its value
- Relation between Page Split and Fill Factor
Latest posts by Manvendra Deo Singh (see all)
- Understanding Hybrid Buffer Pool in SQL Server - December 30, 2018
- Fix:VIEW SERVER STATE permission was denied on object ‘server’, database ‘master’ - August 30, 2018
- SQL Server Error 5184: Cannot use file ‘I:\Path\Techyaz.mdf’ for clustered server - August 29, 2018