MSDN Documentation

Database File Structures in SQL Server

Understanding the file structures of SQL Server databases is crucial for efficient management, performance tuning, and troubleshooting. SQL Server utilizes two primary types of files for each database:

Primary Data Files (.mdf)

The primary data file (.mdf) contains the startup information for the database and all other data and objects. Every SQL Server database must have one primary data file.

• Purpose: Holds system metadata, tables, indexes, and other database objects.
• Naming Convention: Typically named with a .mdf extension, reflecting the database name (e.g., MyDatabase.mdf).
• Location: Can be placed on different physical drives for performance and manageability.
• Single Instance: A database can have only one primary data file.

Secondary Data Files (.ndf)

Secondary data files (.ndf) are optional and are used to extend the storage capacity of a database. A database can have zero or more secondary data files.

• Purpose: Distributes data and objects across multiple files and disks, improving I/O performance and manageability.
• Naming Convention: Typically named with a .ndf extension (e.g., MyDatabase_Secondary.ndf).
• Flexibility: Allows for flexible data distribution. You can add or remove .ndf files as needed.
• Multiple Instances: A database can have multiple secondary data files.

Transaction Log Files (.ldf)

Transaction log files (.ldf) record all transactions and database modifications. These files are essential for recovery and maintaining data integrity.

• Purpose: Records all changes made to the database. Used for database recovery (e.g., rolling back transactions, restoring the database).
• Naming Convention: Typically named with a .ldf extension (e.g., MyDatabase_log.ldf).
• Importance: Critical for database reliability. Should be managed carefully to prevent log file growth issues.
• Separate Storage: It is a best practice to place transaction log files on separate physical disks from data files to optimize performance and I/O.

Filegroups

Files are organized into filegroups. Each database has at least one filegroup: the PRIMARY filegroup. Data files (.mdf and .ndf) can be assigned to specific filegroups. Transaction log files are not part of filegroups but are managed independently.

The PRIMARY filegroup contains the .mdf file and any secondary files assigned to it. You can create additional filegroups to partition data and improve performance by spreading I/O across multiple disks.

Example Scenario

Consider a large database named SalesData:

• SalesData.mdf: The primary data file, containing system metadata and potentially a portion of the sales tables.
• SalesData_Index.ndf: A secondary data file containing indexes, placed on a faster drive.
• SalesData_Archive.ndf: Another secondary data file for older sales records, placed on a slower, larger drive.
• SalesData_log.ldf: The transaction log file, residing on its own dedicated high-speed disk.

By strategically placing these files, you can optimize query performance for frequently accessed data and indexes, while managing storage costs for archival data. Proper planning of file structures is fundamental to robust SQL Server database design.