You may have already seen many posts on same topics as i experienced but i never got all of my answers in single post hence i thought to write this post.
Question: Is it online or offline operation?
Answer: It's online operation with few consideration. Also it's strongly recommended to do this during non-business hours if you are doing it in production and that too for huge tables.
You can follow below mentioned steps to move a table to a particular file group. A FILE-GROUP may contain one or more data files. If the FILE-GROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILE-GROUP. On the other hand, if the FILE-GROUP contains a single data file, then you can say that you have moved the table to a particular file.
To move a table to a particular file-group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILE-GROUP.
Database Name:[AdventureWorks2012]
Table Name: [Person].[Person]
Important Note:
You can not add or remove a file while a BACKUP statement is running.
Create New File-Group:
USE Master;
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP FG_Secondary
GO
Add file to Newly created File-Group:
ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = FG_Secondary,
FILENAME = 'F:\mpdbs001\sqlappdb_V4RC11\AdventureWorks2012_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB
) TO FILEGROUP FG_Secondary;
GO
Select the table name and press Alt+ F1 and verify the data located on file-group column.
Currently it is on PRIMARY file-group.
Run Below mentioned script to move table named [Person].[Person] to filegroup named FG_Secondary.
CREATE Unique CLUSTERED INDEX [PK_Person_BusinessEntityID]
ON [Person].[Person]
(
[BusinessEntityID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON FG_Secondary
GO
-----------------You may experience below mentioned error ----------
Error: Could not drop the primary key constraint 'PK_Person_BusinessEntityID' because the table has an XML or spatial index.
Solution : Script out and Drop the XML Index First and then drop the Primary Key Constraint
-----------------------------------------------------------------------
You can see in below screen nip that table is now reside in secondary file group.
Question: Is it online or offline operation?
Answer: It's online operation with few consideration. Also it's strongly recommended to do this during non-business hours if you are doing it in production and that too for huge tables.
You can follow below mentioned steps to move a table to a particular file group. A FILE-GROUP may contain one or more data files. If the FILE-GROUP contains more than one data file, SQL Server uses a proportional fill algorithm to make use of all data files in the FILE-GROUP. On the other hand, if the FILE-GROUP contains a single data file, then you can say that you have moved the table to a particular file.
To move a table to a particular file-group, you need to re-create the clustered index on the table. Recreating an index effectively moves the table to the new FILE-GROUP.
Database Name:[AdventureWorks2012]
Table Name: [Person].[Person]
Important Note:
You can not add or remove a file while a BACKUP statement is running.
Create New File-Group:
USE Master;
GO
ALTER DATABASE [AdventureWorks2012]
ADD FILEGROUP FG_Secondary
GO
Add file to Newly created File-Group:
ALTER DATABASE [AdventureWorks2012]
ADD FILE
(
NAME = FG_Secondary,
FILENAME = 'F:\mpdbs001\sqlappdb_V4RC11\AdventureWorks2012_Data2.ndf',
SIZE = 500MB,
MAXSIZE = 1000MB,
FILEGROWTH = 50MB
) TO FILEGROUP FG_Secondary;
GO
Select the table name and press Alt+ F1 and verify the data located on file-group column.
Currently it is on PRIMARY file-group.
Run Below mentioned script to move table named [Person].[Person] to filegroup named FG_Secondary.
CREATE Unique CLUSTERED INDEX [PK_Person_BusinessEntityID]
ON [Person].[Person]
(
[BusinessEntityID] ASC
)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, DROP_EXISTING = ON, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON)
ON FG_Secondary
GO
-----------------You may experience below mentioned error ----------
Error: Could not drop the primary key constraint 'PK_Person_BusinessEntityID' because the table has an XML or spatial index.
Solution : Script out and Drop the XML Index First and then drop the Primary Key Constraint
-----------------------------------------------------------------------
You can see in below screen nip that table is now reside in secondary file group.
Hope it will help you.
Warm Regards,
Chhavinath Mishra
Sr. Specialist Database Administrator
