Nisarg Upadhyay
View list of filegroup of SQL database

Managing file groups of SQL Databases

August 8, 2022 by

IN this article, we are going to learn how we can manage the filegroup of SQL Database in SQL Server. The SQL Server has four filegroups.

  1. Primary Filegroup: The primary filegroup is a default filegroup. When we create a new SQL database, the Primary filegroup is automatically created.
  2. Secondary/User-defined filegroups: The secondary filegroup is created by a user. The secondary filegroup is used to manage the data of the database. If you want to keep your highly accessed tables in faster disks, you can create a secondary filegroup and a table in it.
  3. Memory-optimized filegroup: The memory-optimized filegroup is used to store the In-memory OLTP tables and table variables. To create a memory-optimized table in a SQL database, we must create a memory-optimized filegroup. We will learn more about the memory-optimized filegroups in the next article.
  4. FILESTREAM filegroup: The FILESTREAM filegroup is created to host the FILESTREAM data and FILETABLES. I will explain more about the FILESTRAEAM filegroup in the next article.

The filegroup contains one or multiple data files. This article explains how we can manage the We are going to understand the following use cases.

  1. Add data files in filegroups
  2. Rename the filegroup
  3. Change default filegroup

To demonstrate the above scenarios, I have installed SQL Server 2019 on my laptop and restored a stackoverflow2010 sample database. You can download the Stackoverflow2010 database from here. I have also created a filegroup named FG_POSTS in a database. You can view the list of filegroups and list of datafiles associated filegroup by executing the following query.

Output

View list of filegroup of SQL database

Now, let us understand all use cases.

Add data files in a filegroup

We can use ALTER DATABASE ADD FILE TO FILEGRAOUP statement. The syntax is following:

In the syntax,

  1. db_name: Specify the name of the database in which you want to add a data file. The db_name must be specified after ALTER DATABASE keyword.
  2. logical_file_name: Specify the logical name of the secondary data file.
  3. file_location: Specify the path of the data file.
  4. file_size: Specify the initial size of the data file. The unit of the Size parameter can be KB/MB/GB.
  5. datafile_growth: Specify the growth of the data file. The unit of the FILEGROWTH parameter can be KB/MB/GB
  6. file_group_name: Specify the name of the filegroup in which you want to add a data file.

For example, we want to add a data file to the FG_POSTS filegroup with the following options

  1. logical file name: fg_posts_data
  2. datafile location: D:\FG_Posts
  3. data_file_name: fg_posts_data.ndf
  4. initial Size: 10MB
  5. growth: 5MB.

The query to add a data file is following.

Once the command is executed, successfully open the D:\FG_Posts to view the data file.

View secondary data file

As you can see, the file has been created. Alternatively, you can run the following query to view the filegroups.

Query Output

View filegroup in SQL Database

As you can see, the data file named fg_posts_data has been created.

Now, let us understand how we can rename the existing filegroup.

Rename existing filegroup

You can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax:

In the syntax,

  1. db_name: Specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement.
  2. file_group_name: Specify the filegroup name that you want to rename.
  3. file_group_new_name: Specify the new name of the filegroup.

Suppose you want to rename the filegroup named FG_Posts to FG_POSTS_1. The query to rename the filegroup the command is following:

Once the command is executed successfully, run the following query to verify the changes have been applied or not.

Output

Filegroup have been renamed

As you can see, the filegroup has been renamed successfully.

Now, let us see how we can mark a secondary filegroup as a default filegroup.

Change default filegroup

To change the default filegroup, we can use ALTER DATABASE MODIFY FILEGROUP statement. Following is the syntax:

In the syntax,

  1. db_name: Specify the database name in which the filegroup has been created. The database name must be specified after ALTER DATABASE statement.
  2. File_group_name: Specify the filegroup name that you want to make as a default filegroup. The filegroup name must be specified after MODIFY FILEGROUP keyword.
  3. DEFAULT: The DEFAULT keyword must be specified after the filegroup name.

Suppose you want to set the FG_POSTS_1 as a default filegroup. To do that, run the following query.

Once the command is executed successfully, run the following query to view the default filegroup of a database named Stackoverflow2010.

Query output

View default filegroup

As you can see, the FG_POSTS_1 is a default filegroup.

Now, let us create a table named tblStackoverflow_Users. The script to create the table is the following:

Now, let us see in which filegroup the table has been created. To do that, run the following query:

Output

Filegroup name of SQL database

As you can see, the table is created in the FG_POSTS_1 filegroup.

Summary

In this article, we learned how we could manage the filegroups in SQL Database. We learned the following scenarios:

  1. Add data files in filegroups
  2. Rename the filegroup
  3. Change default filegroup

In my upcoming articles, we will learn how we can add FILESTREAM filegroup and memory-optimized file groups in a SQL Database.

Nisarg Upadhyay
General database design, Server management

About Nisarg Upadhyay

Nisarg Upadhyay is a SQL Server Database Administrator and Microsoft certified professional who has more than 8 years of experience with SQL Server administration and 2 years with Oracle 10g database administration. He has expertise in database design, performance tuning, backup and recovery, HA and DR setup, database migrations and upgrades. He has completed the B.Tech from Ganpat University. He can be reached on nisargupadhyay87@outlook.com

816 Views