Home » Blog » SQL Server » What are Files and Filegroups in SQL Server

What are Files and Filegroups in SQL Server

Neeraj Kumar
Published By Neeraj Kumar
Debasish Pramanik
Approved By Debasish Pramanik
Published On
Last Updated

Files and filegroups in SQL Server store every database as data files and log files. Three file types exist and they are the primary data file (MDF), the secondary data files (NDF) and the transaction log (LDF). Filegroups collect data files for placement and administration and exactly one filegroup serves as the default. Every file belongs to one filegroup at most and log files belong to none. Damaged MDF or NDF files need recovery software when no backup exists.

Most readers who search for files and filegroups in SQL Server meet MDF, NDF and PRIMARY in a CREATE DATABASE script or in Database Properties and want plain meanings.

You get statements for files and filegroups in SQL Server that you can execute on a test server, plus six design rules near the end.

Microsoft states that every database has at least two operating system files and they are a data file and a log file.

What are the Three Types of Database Files?

Microsoft recommends one extension for every file type in files and filegroups alike. SQL Server does not force the extension.

FileRoleExtensionCount
Primary data fileStartup details and links to every other file.mdfExactly one
Secondary data fileOptional user defined data.ndfAny number
Transaction logRecords SQL Server uses to recover transactions.ldfAt least one

Microsoft states that data and log files share the same disk and path by default and calls that setup suboptimal for production. Separate disks are the recommendation.

What are Logical and Physical File Names?

Every file has two names. Logical names appear in T-SQL statements and must be unique inside the database. Physical names are the operating system paths of the files.

CREATE DATABASE TicketDB
ON PRIMARY
  (NAME = N'TicketDB', FILENAME = N'E:\SQLData\TicketDB.mdf')
LOG ON
  (NAME = N'TicketDB_log', FILENAME = N'F:\SQLLogs\TicketDB_log.ldf');

TicketDB and TicketDB_log are logical names in the statement above. Both paths are physical names.

What are Filegroups in SQL Server?

Filegroups are named sets of data files. Microsoft describes five kinds in its database files and filegroups reference.

FilegroupWhat It Contains
PrimaryThe primary data file and any secondary file placed in no other filegroup. All system tables live here
User definedData files grouped for administrative, data allocation and placement purposes
DefaultThe filegroup that receives new objects with no filegroup. PRIMARY is the default until you set another
FILESTREAMUnstructured data stored in file system directories
Memory optimizedData of memory optimized tables on top of a FILESTREAM filegroup

Three rules apply to files and filegroups:

  • No file or filegroup can serve more than one database.
  • Every file belongs to one filegroup at most.
  • Transaction log files never join a filegroup.

What is the Default Filegroup?

Exactly one filegroup is the default at any time. New tables and indexes go into it when the statement names no filegroup. System objects remain in PRIMARY even after you set another default.

Set a new default with ALTER DATABASE.

ALTER DATABASE TicketDB
MODIFY FILEGROUP FG_Archive DEFAULT;

How to Create a Filegroup and Add a File?

Three statements create a filegroup with one secondary file and a table that uses it.

ALTER DATABASE TicketDB
ADD FILEGROUP FG_Archive;

ALTER DATABASE TicketDB
ADD FILE (NAME = N'TicketDB_Archive1',
          FILENAME = N'G:\SQLData\TicketDB_Archive1.ndf',
          SIZE = 100MB, FILEGROWTH = 50MB)
TO FILEGROUP FG_Archive;

CREATE TABLE dbo.ClosedTickets
(ticket_id INT PRIMARY KEY, closed_on DATE)
ON FG_Archive;

How to View Files and Filegroups in SQL Server?

SSMS shows both lists on the Files and Filegroups pages of Database Properties. T-SQL gives the same result in one query.

SELECT f.name AS file_name, f.type_desc,
       g.name AS filegroup_name, f.physical_name
FROM sys.database_files AS f
LEFT JOIN sys.filegroups AS g ON g.data_space_id = f.data_space_id;

Log files show no filegroup name because they belong to none.

How do File Growth and Proportional Fill Work?

Four behaviors decide how files and filegroups fill according to Microsoft:

  • Growth Increment. Files autogrow by the increment you set every time they fill.
  • Maximum Size. A file without a maximum size grows until it uses all available disk space.
  • Multiple Files. Files in a filegroup do not autogrow until all of them are full.
  • Proportional Fill. SQL Server spreads data across the files by free space. SQL Server takes one extent from the file with 100 MB free for every two extents from the file with 200 MB free.

Microsoft lists the AUTOGROW_ALL_FILES option for SQL Server 2016 and later. Check the is_autogrow_all_files column of sys.filegroups for the setting. Value 1 means all files in the filegroup grow when one meets the threshold.

What are the Size Limits of Files and Filegroups in SQL Server?

ItemLimit
Page size8 KiB
Extent size64 KiB with eight contiguous pages
Data file size16 terabytes
Log file size2 terabytes
Files per database32,767
Filegroups per database32,767
Database size524,272 terabytes

Sources are the Microsoft pages and extents guide and the maximum capacity specifications.

Which Design Rules Fit Files and Filegroups in SQL Server?

Six recommendations guide the layout of files and filegroups:

  • Start Small. Most databases work well with one data file and one log file.
  • Second Filegroup. Add a second filegroup for extra data files and make it the default so PRIMARY contains only system tables and objects.
  • Spread the Disks. Spread files and filegroups across disks and place competing objects in different filegroups.
  • Join Tables. Put tables used in the same join queries in different filegroups for parallel disk access.
  • Index Split. Put heavily accessed tables and their nonclustered indexes in different filegroups.
  • Log Disk. Place log files on a physical disk that has no other files or filegroups.

Microsoft states that an NTFS compressed file system accepts only READ_ONLY databases and READ_ONLY secondary filegroups. Filegroups that accept updates and log files are not supported there. Use data compression instead.

How to Recover Damaged Primary and Secondary Files?

Recover the Files With EmailDoctor SQL Database Recovery

Damaged primary or secondary files stop the attach. Without a backup the data needs recovery software. EmailDoctor SQL Database Recovery scans the MDF and the NDF files in one pass and lists the recovered objects for a preview ahead of the export.

Trial exports are limited to 25 rows from a table and 5 other objects. Scan copies of the damaged files.

Add the Database Files

  1. Launch the software on a Windows computer. Software home screen
  2. Click Open and select your TicketDB.mdf copy. Open button at the top MDF file selection window
  3. Begin with Quick Scan and let Auto detect find the version. Quick Scan mode
  4. Open NDF Options and add the secondary files of the other filegroups. NDF Options tab Choose NDF files
  5. Review the scan report. Scan report
  6. Click any table to preview its rows in the right pane. Recovered table preview

Save the Recovered Objects to SQL Server

  1. Open the Export menu and choose the SQL Server database item. Export menu
  2. Choose the destination server from the list. Server name list
  3. Choose Create New Database and name it TicketDB_Recovered. Create New Database option Database name box
  4. Set With Schema & Data and click Export. Schema and data options
  5. Confirm the message and refresh SSMS to see the tables. Export Completed message

Frequently Asked Questions

Can a Database Have More Than One Primary Data File?

No. Microsoft states that a database has exactly one primary data file. Add NDF files for more space.

Does a Database Need NDF Files?

No. Secondary data files are optional. Most databases work well with one data file and one log file.

Can a File Belong to Two Filegroups?

No. Every file is a member of one filegroup only.

Do Log Files Belong to a Filegroup?

No. Microsoft states that transaction log files are never part of any filegroup.

Is PRIMARY the Default Filegroup?

Yes until you set another with ALTER DATABASE. System objects remain in PRIMARY even after that step.

Can I Set Another Default Filegroup?

Yes. Use ALTER DATABASE with MODIFY FILEGROUP and the DEFAULT keyword. Exactly one filegroup is the default at any time.

Can Two Databases Share One File?

No. Microsoft states that a file or filegroup cannot be used by more than one database.

Is There a Limit on Database Files?

Yes. Microsoft lists 32,767 files and 32,767 filegroups per database. Data files reach 16 terabytes and log files reach 2 terabytes.

Can I Use NTFS Compression on Data Files?

No for filegroups that accept updates. Only READ_ONLY databases and READ_ONLY secondary filegroups qualify. Use data compression for space savings.

Can Recovery Software Open MDF and NDF Files Together?

Yes. Use the NDF Options tab to add the NDF files ahead of the scan. Review the preview before saving anything.

Does the Trial Version Cap the Export?

Yes. Trial exports stop at 25 rows from one table and 5 more objects. Licensing removes the limit.

Author’s Verdict

Files store the data and the log while filegroups group the data files for placement and administration.

Three rules matter most. Every file serves one database and one filegroup while logs join none.

Recovery software restores the objects of damaged MDF and NDF files when no backup exists. Test every step on a copy first.

  • Primary data files start the database and secondary files add space.
  • Log files never join a filegroup and every data file joins exactly one.
  • PRIMARY is the default filegroup until you set another.
  • Microsoft advises a second default filegroup so PRIMARY contains only system objects.
  • Recovery software restores damaged MDF and NDF files into a new database.
  • Work on copies.
  Neeraj Kumar

By Neeraj Kumar

Neeraj Kumar is a Principal Technical Architect at EmailDoctor with 19+ years of experience in email migration, cloud migration, data recovery and digital forensics. He guides enterprise teams through Exchange, Office 365 and Google Workspace projects and shares what works as an EmailDoctor writer. Every method in his posts is tested on Windows and Mac before it goes live.