Database Files, Filegroups, MDF/NDF/LDF, Autogrowth, Instant File Initialization, and Layout

Learning outcomes

A ServiceHub capacity incident shows the database “ran out of disk,” but the first incident note says only “the MDF grew.” That sentence hides three separate design elements: data files, transaction-log files, and filegroups. SQL Server allocates data and log differently, grows them differently, initializes them differently, and uses them for different recovery purposes. File extensions help humans recognize conventions; the catalog defines what a file actually is.

1. A database has data files and a transaction log

SQL Server user databases normally have at least one data file and one transaction-log file. Data files hold pages that belong to tables, indexes, allocation structures, and other database objects. The log records changes needed for transactional correctness and recovery. The log is not a second copy of the data file, and adding more data files does not “spread the transaction log.”

Data files belong to filegroups. The PRIMARY filegroup contains the primary data file and can contain additional data files. Additional filegroups let an administrator place selected objects or partitions into separately managed storage groups. Transaction-log files do not belong to data filegroups. Later backup/recovery chapters explain when file/filegroup backups are operationally useful.

2. MDF/NDF/LDF are conventions; catalog type is authority

Common naming uses .mdf for a primary data file, .ndf for secondary data files, and .ldf for log files. SQL Server does not infer file semantics from those extensions. A misleading filename can still be registered as a data or log file because the database metadata defines the file type.

USE ServiceHubLab;GOSELECT    file_id,    name AS logical_name,    type_desc,    data_space_id,    physical_name,    size * 8.0 / 1024 AS size_mb,    CASE WHEN max_size = -1 THEN NULL ELSE max_size * 8.0 / 1024 END AS max_size_mb,    growth,    is_percent_growthFROM sys.database_filesORDER BY file_id;GOSELECT    fg.data_space_id,    fg.name AS filegroup_name,    fg.type_desc,    fg.is_defaultFROM sys.filegroups AS fgORDER BY fg.data_space_id;GO

Use logical names in ALTER DATABASE ... MODIFY FILE; physical paths are server-side filesystem paths. A path visible to SSMS on your laptop is irrelevant if the Database Engine runs in a container or on a remote Linux/Windows host.

3. Autogrowth is an emergency capacity mechanism, not capacity planning

A file’s configured size is allocated storage. Autogrowth allows SQL Server to expand a file when more space is needed, but growth happens during workload execution and can create latency, filesystem pressure, or disk-full incidents. Pre-sizing a database from observed workload growth reduces the number of surprise growth events; keeping sensible autogrowth enabled still provides headroom when reality exceeds the forecast.

Fixed-size growth increments are usually easier to reason about than percentages because a percentage produces larger and larger increments as the file grows. Avoid universal growth numbers: a 64-MB increment can be sensible in one lab and pathological for a multi-terabyte workload. Measure normal/peak growth, storage throughput, maintenance operations, backup/restore needs, and free capacity.

USE ServiceHubLab;GOSELECT    df.file_id,    df.name,    df.type_desc,    df.size * 8.0 / 1024 AS allocated_mb,    CASE      WHEN df.type_desc = 'ROWS'      THEN FILEPROPERTY(df.name, 'SpaceUsed') * 8.0 / 1024      ELSE NULL    END AS data_space_used_mb,    df.growth,    df.is_percent_growth,    df.max_sizeFROM sys.database_files AS dfORDER BY df.file_id;GO

FILEPROPERTY(...,'SpaceUsed') is meaningful for data files. Transaction-log utilization uses different evidence because log reuse depends on VLF/log-chain/recovery behavior, not “unused pages” in a data file. Chapter 08 covers the log and virtual log files (VLFs) in depth.

4. Instant file initialization: current behavior is more nuanced than old folklore

Instant file initialization (IFI) allows SQL Server to allocate data-file space without first zeroing every newly allocated byte, which can make data-file creation/growth/restore operations much faster. On Windows, data-file IFI depends on the Database Engine service account or service SID having the Perform volume maintenance tasks privilege. Transparent Data Encryption can affect data-file IFI behavior, so verify the exact current feature combination.

Old advice often says “the transaction log can never use instant file initialization.” That is no longer universally correct. Starting with SQL Server 2022 and continuing in SQL Server 2025, transaction-log autogrowth events up to and including 64 MB can benefit from instant file initialization. Larger log autogrowth events are still zero-initialized. This is exactly why the course requires version-specific verification rather than memorized rules.

SELECT    servicename,    status_desc,    startup_type_desc,    instant_file_initialization_enabledFROM sys.dm_server_servicesWHERE servicename LIKE N'SQL Server (%';GO

On SQL Server 2022 and later this DMV requires VIEW SERVER SECURITY STATE. If a least-privilege lab login cannot query it, that is expected permission evidence—do not grant sysadmin merely to make the screen match the lesson. The SQL Server error log also records IFI state at startup.

5. Deliberately wrong approach: add files until performance improves

An operator sees high latency, creates four extra data files on the same saturated volume, and declares the database “striped.” Filegroups/files can influence allocation and placement, but extra files do not create physical I/O bandwidth by themselves. On the same storage device, every file can still contend for the same underlying latency/throughput. Conversely, multiple files can be useful for specific allocation, manageability, partitioning, tempdb, or storage-layout goals—but those are explicit designs, not numerology.

6. Hands-on lab: create a disposable file-layout database

USE master;GOIF DB_ID(N'ServiceHubFileLab') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubFileLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubFileLab;END;GOCREATE DATABASE ServiceHubFileLab;GOALTER DATABASE ServiceHubFileLabMODIFY FILE(    NAME = N'ServiceHubFileLab',    FILEGROWTH = 64MB);GOALTER DATABASE ServiceHubFileLabMODIFY FILE(    NAME = N'ServiceHubFileLab_log',    FILEGROWTH = 64MB);GOALTER DATABASE ServiceHubFileLabADD FILEGROUP FG_SERVICEHUB_ARCHIVE;GOUSE ServiceHubFileLab;GOSELECT file_id, name, type_desc, physical_name,       size * 8.0 / 1024 AS size_mb,       growth, is_percent_growthFROM sys.database_filesORDER BY file_id;GOSELECT data_space_id, name, type_desc, is_defaultFROM sys.filegroupsORDER BY data_space_id;GO

The empty FG_SERVICEHUB_ARCHIVE proves that a filegroup is a logical allocation container; it does not store data until it has a data file and objects allocated to it. We deliberately do not add a physical secondary data file because a portable course cannot guess a safe server-side filesystem path for Windows, Linux, containers, or remote hosts.

USE master;GOIF DB_ID(N'ServiceHubFileLab') IS NOT NULLBEGIN    ALTER DATABASE ServiceHubFileLab SET SINGLE_USER WITH ROLLBACK IMMEDIATE;    DROP DATABASE ServiceHubFileLab;END;GO

Verification checklist

• You can identify data versus log files from type_desc, not their extension.
• You can distinguish logical file name from physical server path.
• You can explain why a data filegroup does not contain log files.
• You can state the SQL Server 2022+ ≤64-MB log-growth IFI exception to the old “logs never initialize instantly” rule.
• You did not shrink a log, guess a production path, or add files as a performance superstition.

7. Production judgment and next bridge

Production storage design starts with workload and recoverability. Pre-size for normal growth, leave controlled headroom, monitor remaining volume capacity, choose growth increments that the storage system can satisfy without long stalls, and alert on both file-level and volume-level pressure. Separate data and log placement only when the underlying storage topology makes the separation meaningful. Filegroups are also useful for manageability and advanced backup/partitioning strategies, but complexity must pay for itself operationally.

The next lesson asks a parallel question about configuration: which setting is instance-scoped, database-scoped, session-scoped, startup-persistent, or temporary? Just as a filename does not tell you file semantics, a value shown in one GUI does not tell you its scope, provenance, persistence, or effective state.

Authoritative references

• Database files and filegroups — data/log files, logical/physical names, and filegroup concepts
• Database instant file initialization — current data-file and SQL Server 2022+ log-growth IFI behavior
• sys.dm_server_services — observable IFI state and SQL Server 2022+ permission requirement
• Manage transaction log file size — growth sizing and log-file operational guidance