Add a Filegroup to a Database in SQL Server
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
0 Comments