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.

Database files in SQL Server generally operate with 2 files, data (.mdf) files and log (.ldf) files. If we add an additional data file, we call it a user-defined (.ndf) data file. Filegroups are a grouping of these user-defined data files.

Database Administrators may add new data files with the aim to improve the performance of a database or several databases on an instance, splitting the data files and data onto more than one disk drive. Another reason you might be adding a new data file in SQL Server is when you are configuring Change Data Capture (CDC), it’s a best practice to create a separate Filegroup for the CDC data.

Filegroups in SQL Server are a grouping of user-defined data files. The demo below includes creating a new Filegroup with 2x data files. For more information and descriptions on Filegroups, see Microsoft Docs: Filegroups & MS SQL Database File Recommendations

Add New Filegroup for a Database

The SQL below will create a Filegroup and add 2x new files for storing the data. More info on syntax – Microsoft Docs: ALTER DATABASE File and Filegroup Options

We are creating a demo database and adding a Filegroup here:

-- Create a test database
CREATE DATABASE [sqlDBA];

USE [sqlDBA];
GO
-- Create test tables & insert rows from system tables
SELECT * INTO dbo.raw_sysdatabases FROM sys.databases;
SELECT * INTO dbo.raw_sysobjects FROM sys.system_objects;

-- Create a filegroup
ALTER DATABASE [sqlDBA] ADD FILEGROUP [sqlDBA_FG1];
-- Check filegroups for current database
SELECT * FROM sys.filegroups;

Now we add the new data file(s) to the Filegroup in the sqlDBA database:

-- 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;

To check database files via GUI, go to the Shrink Databases option in SQL Server Management Studio.

SSMS > Right-Click Database > Tasks > Shrink:

If you’re wondering what kind of data would be useful to have in a database filegroup, here’s an example from my other recent blog post where we create a filegroup for CDC data – How to Enable Change Data Capture (CDC) in SQL Server