How to move Tables/objects between Filegroups

In this post, I will be explaining how to move tables/objects between different FileGroups.

  • Create a sample database
USE master
GO
CREATE DATABASE MoveFG
GO
  • Create Two file groups. In this example i have created two  filegroups MoveFG_DATA_1 and MoveFG_DATA_2
ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_1
GO
ALTER DATABASE MoveFG ADD FILEGROUP MoveFG_DATA_2
GO
  • Create data files in different filegroups. In this example, i have created four data files, two for each filegroup
ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG11,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_1
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG20,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO

ALTER DATABASE MoveFG
ADD FILE
( NAME = MoveFG21,
FILENAME = 'C:\Ram\MoveFGDB\MoveFG_21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 1MB)
TO FILEGROUP MoveFG_DATA_2
GO
  • Now, create a table on filegroup MoveFG_DATA_1 and later move the filegroup to another group name MoveFG_DATA_2
USE MoveFG
GO

CREATE TABLE TAB1
(
TAB1_ID INT IDENTITY(1,1),
TAB1_NAME VARCHAR(100),
CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
) ON MoveFG_DATA_1 -- Filegroup we created.
GO
  • Insert few records into the table TAB1
SET NOCOUNT OFF
INSERT INTO TAB1(TAB1_NAME)
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE'
GO 10000
  • Verify which file group the data resides. You can use sp_help to get the details as shown in below.

1.png

  • As you can see above screenshot the table is on First file group MoveFG_DATA_1. Let us move the table TAB1 to another filegroup name MoveFG_DATA_2. To accomplish this we need to alter table with drop constraint with move option to move to another file group as shown below
ALTER TABLE TAB1 DROP CONSTRAINT PK_TAB1 WITH (MOVE TO MoveFG_DATA_2)
GO
ALTER TABLE TAB1 ADD CONSTRAINT PK_TAB1 PRIMARY KEY(TAB1_ID)
GO
  • Now, Check which file group the table TAB1 resides

2

  • There you go, The table has been moved to secondary file group MoveFG_DATA2. You can also use below query to get the details of tables/objects on different filegroups. I have taken this from Pinal’s Blog
SELECT obj.[name], obj.[type], i.[name], i.[index_id], fg.[name] FROM sys.indexes i
INNER JOIN sys.filegroups fg
ON i.data_space_id = fg.data_space_id
INNER JOIN sys.all_objects obj
ON i.[object_id] = obj.[object_id] WHERE i.data_space_id = fg.data_space_id
AND obj.type = 'U' -- User Created Tables
GO

3.PNG

  • Be careful running this code on a production system, You may want to consider running this code during a maintenance window so the users are not impacted.

Hope you like the post. Stay tuned for next tip.

Ramasankar Molleti

MSDN:LinkedIn:Twitter

Published by Ramasankar

Hi. I’m Ramasankar Molleti. I’m a passionate IT professional with over 14 years of experience on providing solutions for customers who are looking on cloud computing, Database Migration, Development, and Big Data. I love learning new technologies and share my knowledge to community. I am currently working as Sr Cloud Architect with focus on Cloud Infrastructure, Big Data. I work with developers to architect, build, and manage cloud infrastructure, and services. I have deeep knowledge and experience on working with various database platforms such as MS SQL Server, PostgeSQL, Oracle, MongoDB, Redshift, Dyanamodb, Amazon Aurora. I worked as Database Engineer, Database Administrator, BI Developer and successfully transit myself into Cloud Architect with focus on Cloud infranstructure and Big Data. I live in USA and put my thoughts down on this blog. If you want to get in touch with me, contact me on my Linkedin here: https://www.linkedin.com/in/ramasankar-molleti-23b13218/ My Certifications: Amazon: AWS Certified Solutions Architect – Professional AWS Certified DevOps Engineer – Professional certificate AWS Certified Big Data – Specialty AWS Certified Security – Specialty certificate AWS Certified Advanced Networking – Specialty certificate AWS Certified Solutions Architect – Associate Microsoft: Microsoft® Certified Solutions Associate: SQL Server 2012/2014 Microsoft Certified Professional Microsoft® Certified IT Professional: Database Administrator 2008 Microsoft® Certified Technology Specialist: SQL Server 2008, Implementation and Maintenance

One thought on “How to move Tables/objects between Filegroups

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: