Add a Filegroup to a Database in SQL Server

This post contains a demo on how to add a new Filegroup for a database in SQL Server. Most databases work well with single data/log files and do not need this.

Adding filegroups to a SQL Server database can improve performance by distributing data across multiple disk drives or supporting specific features like Change Data Capture (CDC). While many databases perform well with default data (.mdf) and log (.ldf) files, introducing user-defined data files (.ndf) within filegroups can optimize storage and organisation.

Here’s a quick summary of when to use Filegroups:
> Performance Improvements:
Spreading data across multiple disk drives can reduce I/O contention.
> Data Organization:
Isolating specific data types or features (e.g., CDC) in separate filegroups.
> Scalability:
Efficiently managing large databases by segmenting data into logical groups.

For further explanations on SQL Server Filegroups, check out this MS Docs page. And while you’re at it you could be reading about the SQL Server Database File Recommendations.

Adding New Filegroup for a Database

In the demo below we’re adding 2x new files to a databases. I’m going to start this demo by creating a sample database and adding some tables:

Next, I’m going to add the new Filegroup by running the following:

-- Create data files for Filegroup
ALTER DATABASE [sqlDBA]
ADD FILE
(
    NAME = sqldba_data1,
    FILENAME = 'D:\mssql_data\sqldba_data1.ndf',
    SIZE = 500MB,
    FILEGROWTH = 50MB
),
(
    NAME = sqldba_data2,
    FILENAME = 'D:\mssql_data\sqldba_data2.ndf',
    SIZE = 500MB,
    FILEGROWTH = 50MB
) TO FILEGROUP [SQLDBA_FG1];

-- Check files for a database
USE [sqlDBA];
GO
SELECT 
	file_id, type, type_desc, data_space_id, name, 
	physical_name, state, size, max_size, is_percent_growth, growth
FROM sys.database_files;

I’ll now check the sys.database_files system table to verify the new Database Filegroup:

-- get database file information
SELECT  
  file_id, type, type_desc, data_space_id, name,  
  physical_name, state, size, max_size, is_percent_growth, growth  
FROM sys.database_files;

To view or manage database files through the SSMS interface:
> Select Tasks > Shrink > Files to inspect the files and their configuration.
> Right-click the database in SSMS.

When to Use Filegroups

A common use case for filegroups is isolating CDC data, as explained in How to Enable Change Data Capture (CDC) in SQL Server.

By adding filegroups thoughtfully, you can enhance database performance, improve data management, and prepare your system for future scaling needs. Hope this helps!