Narcis Miclaus

7 min read

SQL Server, from the ground up Lesson 30 / 40

Filegroups, data files, and what the log file is really doing

MDF, NDF, LDF, autogrowth disasters, VLFs, and why full recovery model without log backups is a ticking bomb. The storage layout every DBA should know.

Every SQL Server database has at least two files on disk: one data file (.mdf) and one log file (.ldf). Most people go years without thinking about them. Then one day the disk fills up, the database goes offline, and someone has to explain to the CEO why the Runehold website is returning 500s.

Today we demystify the files. What they are, how they grow, why the log file is a separate thing from the data, and the one setting that every DBA eventually wishes they’d configured earlier: autogrowth.

The files

A fresh database has:

  • Primary data file, .mdf — the main data container. One per database.
  • Transaction log file, .ldf — records every change; essential for recovery.
  • Optionally, secondary data files, .ndf — added to spread data across drives or filegroups.
-- See the files for a database
SELECT
    name,
    physical_name,
    type_desc,
    size * 8 / 1024   AS size_mb,
    growth,
    is_percent_growth,
    max_size
FROM sys.master_files
WHERE database_id = DB_ID('Runehold');

size is in 8-KB pages, hence the * 8 / 1024 to convert to MB. Every system database (master, tempdb, model, msdb) and every user database has entries here.

Filegroups

A filegroup is a named bucket of one or more data files. By default every database has one filegroup called PRIMARY. You can create more:

ALTER DATABASE Runehold ADD FILEGROUP FG_Archive;

ALTER DATABASE Runehold ADD FILE (
    NAME = 'Runehold_Archive',
    FILENAME = 'D:\SQLData\Runehold_Archive.ndf',
    SIZE = 1024MB
) TO FILEGROUP FG_Archive;

Uses of filegroups:

  • Separate hot and cold data. Archive tables go to FG_Archive on cheaper storage.
  • Piecemeal restore. Restore critical filegroups first, bring the database online, restore archive later.
  • Partition placement. The sliding-window pattern from lesson 29 puts each partition on a different filegroup.

You specify a filegroup at table-creation time:

CREATE TABLE Archive.OrdersOld (
    OrderId BIGINT PRIMARY KEY,
    ...
) ON FG_Archive;

Without the ON clause, the table goes to the default filegroup (PRIMARY unless you change it). Change the default:

ALTER DATABASE Runehold MODIFY FILEGROUP FG_Archive DEFAULT;

The log file: what it’s really doing

The log file records every change made to the database. It’s the write-ahead log: before any data page is written, the change is logged first. That’s why durability (the D in ACID) works.

Log content:

  • Every INSERT, UPDATE, DELETE.
  • Every DDL (CREATE, ALTER, DROP).
  • Transaction begins and commits.
  • Checkpoints (markers that say “up to this point, all committed data is on disk”).

The log is circular: SQL Server writes to the end, then wraps around to reuse the front when it’s safe. The log file can grow but never shrinks automatically.

What “safe to reuse” means depends on the recovery model.

Recovery models

Three options:

  • SIMPLE — log is reusable as soon as the transaction commits. No point-in-time recovery. Log stays small automatically. Great for dev/test, dangerous for prod.
  • FULL — log is reusable only after a transaction log backup. Point-in-time recovery possible. Log grows until a log backup happens.
  • BULK_LOGGED — in between; minimal logging for bulk imports, log still needs backups. Niche.
-- Check current
SELECT name, recovery_model_desc FROM sys.databases WHERE name = 'Runehold';

-- Change it
ALTER DATABASE Runehold SET RECOVERY FULL;

The trap: FULL recovery without log backups. The log grows until it fills the disk. Database goes read-only. Everything stops.

Rule: if you’re in FULL recovery, you must be backing up the log regularly (every 15 minutes is a common starting point). If you can’t do that, switch to SIMPLE. FULL without log backups is the worst of both worlds.

We’ll cover backup strategies properly in lesson 31.

Virtual Log Files (VLFs)

Inside the log file, SQL Server divides things into “chunks” called Virtual Log Files. Each autogrowth event creates new VLFs proportional to the growth size.

Too many small autogrowths = too many small VLFs. This slows down recovery (SQL Server has to walk each VLF at startup), slows down log backups, and occasionally causes mysterious performance issues.

Rule of thumb: target around 50-500 VLFs total. Count with:

DBCC LOGINFO;
-- Returns one row per VLF. Count the rows.

If you see 10,000+ VLFs, you’ve had a lot of tiny autogrowth events. Fix by:

  1. Shrink the log to minimum (requires a log backup first if in FULL).
  2. Regrow it in one big chunk to its working size.
BACKUP LOG Runehold TO DISK = 'NUL';         -- if FULL, need a log backup to free space
DBCC SHRINKFILE ('Runehold_log', 100);        -- shrink to 100 MB
ALTER DATABASE Runehold MODIFY FILE
    (NAME = 'Runehold_log', SIZE = 8192MB);   -- pre-grow to 8 GB in one shot

One clean 8 GB file creates about 16 VLFs (the sizing heuristic has changed over versions). A log file that grew by 10MB 800 times has 6,400 tiny VLFs. Life is better with the former.

Autogrowth: the disaster waiting to happen

Every SQL Server database has autogrowth settings: how much to grow by when the file fills up. Defaults:

  • Data files: 64 MB
  • Log files: 64 MB (was 10% in older versions, terrible)

Problems with the defaults:

  • On a busy system, growing by 64 MB means growing every few minutes. Each growth event blocks writes briefly.
  • Many tiny growth events produce the VLF mess above.
  • “Percent” growth on a 500 GB file means a 50 GB growth event. Server pauses, disk fills suddenly, alerts fire.

Recommendation: explicit fixed-size autogrowth, sized for your workload. Runehold’s prod database:

  • Data file: start at 50 GB, autogrow by 2 GB.
  • Log file: start at 8 GB, autogrow by 1 GB.
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_data',
    FILEGROWTH = 2GB
);

Also: pre-size the files to their expected working set at creation time. Don’t start at 8 MB and grow. Tell SQL Server at CREATE DATABASE time “I’ll need 50 GB, reserve it now.” Fewer growth events, better VLF count, less fragmentation on the underlying disk.

Instant file initialization

When SQL Server grows a data file, it has to write zeros to the new space. On a 50 GB growth, that’s 50 GB of zero-writes — seconds to minutes.

Instant file initialization (IFI) skips that zero-write. Microsoft’s docs recommend enabling it. Requires granting the SQL Server service account the Perform Volume Maintenance Tasks Windows privilege. On Linux / container installs, IFI is on by default.

With IFI, a 50 GB data file grow takes ~1 second instead of ~30. Hugely better.

Note: IFI only helps data files. The log file always has to be zeroed for correctness (log reads depend on knowing where valid data ends).

What to monitor

Short list of queries and thresholds worth alerting on:

-- Disk usage per database
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
       size * 8 / 1024 - FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS free_mb
FROM sys.database_files;

-- Log file free percentage
DBCC SQLPERF(LOGSPACE);
-- Returns rows showing Log Space Used (%) per database.

-- VLF count for current database
DBCC LOGINFO;   -- count the rows

-- Autogrowth events in the last 7 days
SELECT database_name, file_logical_name,
       (size_change_kb / 1024) AS size_change_mb,
       start_time
FROM msdb.dbo.backupset a -- placeholder; actual DMV: ring_buffer / default XE session
WHERE 1 = 0;

(To find autogrowth events, the default trace or an extended event session is the right source. SQL Server Management Studio’s Disk Usage report is an easy GUI view.)

Alert when:

  • Disk usage > 85%.
  • Log space used > 75%.
  • An autogrowth event happens on prod.
  • VLF count > 1,000.

Run this on your own machine

USE Runehold;
GO

-- 1. See the files and their sizes
SELECT name, type_desc, size * 8 / 1024 AS size_mb,
       FILEPROPERTY(name, 'SpaceUsed') * 8 / 1024 AS used_mb,
       growth, is_percent_growth
FROM sys.database_files;

-- 2. Log space and VLF count
DBCC SQLPERF(LOGSPACE);
DBCC LOGINFO;

-- 3. Set sensible autogrowth
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold',
    FILEGROWTH = 512MB
);
ALTER DATABASE Runehold MODIFY FILE (
    NAME = 'Runehold_log',
    FILEGROWTH = 256MB
);

-- 4. Make a secondary filegroup + file (demo only, adjust paths)
-- ALTER DATABASE Runehold ADD FILEGROUP FG_Archive;
-- ALTER DATABASE Runehold ADD FILE (
--     NAME = 'Runehold_Archive',
--     FILENAME = 'C:\Data\Runehold_Archive.ndf',
--     SIZE = 256MB, FILEGROWTH = 64MB
-- ) TO FILEGROUP FG_Archive;

-- 5. Check recovery model and backup status
SELECT d.name, d.recovery_model_desc,
       DATEDIFF(HOUR, MAX(bs.backup_finish_date), GETDATE()) AS hours_since_last_full_backup
FROM sys.databases AS d
LEFT JOIN msdb.dbo.backupset AS bs
       ON bs.database_name = d.name AND bs.type = 'D'
WHERE d.name NOT IN ('tempdb', 'model')
GROUP BY d.name, d.recovery_model_desc;

A database in FULL recovery without recent log backups is the number-one “oh no” moment a new DBA walks into. The above query surfaces it immediately.

Module 6 done. Partitioning (lesson 29), filegroups, and the log file. Now the big operational topics: backups, Agent, security, tempdb, maintenance, and DMVs. That’s Module 7.

Search