MDF File Documentation

---

Technical details

Feature	Value
Full name	Master Database File (SQL Server primary data file)
File extension	.mdf
MIME type	application/octet-stream
Format type	Binary paged database data file
Developer	Microsoft (SQL Server storage engine)
Companion files	.ldf (transaction log), .ndf (secondary data files)
Page size	8192 bytes (8 KB)
Page header	96 bytes; ~8060 bytes for rows and slot array
Extent	8 contiguous pages (64 KB)
Page types	Data, index, IAM, PFS, GAM, SGAM, text/image (LOB), boot
Boot page	Page 9 of the primary data file
Max row size	8060 bytes in-row (LOB/overflow beyond that)
Encryption	Transparent Data Encryption (TDE), AES on data at rest
Compression	Row and page compression
Backup types	Full, differential, transaction-log
Opened with	SQL Server (attach); SSMS, Azure Data Studio for access
Also uses this extension	Alcohol 120% / DAEMON Tools disc image (with .mds); ASAM measurement file
Related extensions	.ldf, .ndf, .bak, .mdb

What is an MDF file?

An MDF file, short for Master Database File, is the primary data file of a Microsoft SQL Server database. It is the starting point of every SQL Server database and holds the real contents: the data tables, indexes, stored-procedure and view definitions, and the system metadata that describes them. The file is binary and paged, so it is not readable in a text editor; SQL Server reads and writes it through its storage engine. An MDF is almost always accompanied by an .ldf transaction-log file that records every change, and a database can add secondary .ndf data files that share the same page format.

Because an MDF is a live database file rather than a document, you do not “open” it directly; you attach it to a SQL Server instance, after which SQL Server Management Studio (SSMS) or Azure Data Studio can query it. The sections below go inside the file: the 8 KB page that is the unit of storage, the header every page carries, how pages group into extents, the special allocation and boot pages, and how a single row is laid out on a data page.

The 8 KB page: the unit of storage

The fundamental unit of an MDF is the page, a fixed 8192-byte (8 KB) block. The entire file is an array of these pages, numbered from 0, and SQL Server always reads and writes whole pages rather than individual rows. Every page begins with a 96-byte header, which leaves about 8060 bytes for row data and the slot array. Rows are stored from just after the header growing toward the end of the page, while a slot array grows backward from the very end, so the free space sits in the middle.

page (8192 bytes)
  0      96 bytes   page header (m_pageId, m_type, m_objId, m_lsn, ...)
  96   ~8060 bytes  row data area (rows grow top-down)
  ...              free space
  end   2 bytes/row slot (row-offset) array (grows bottom-up)

This layout is why the maximum size of a single in-row record is 8060 bytes: it is what remains of the page after the header and one slot entry. Rows larger than that push their variable-length or large-object columns off the page onto row-overflow or text/image (LOB) pages, leaving a pointer behind.

The 96-byte page header

Every page’s first 96 bytes are a structured header that the storage engine reads before touching any row. Its fields identify the page, say what it holds, and support recovery and integrity checking:

Field	Meaning
m_pageId	This page’s address as (file number, page number)
m_type	Page type: 1 data, 2 index, 3/4 text/LOB, 8 GAM, 9 SGAM, 10 IAM, 11 PFS, 13 boot, 15 file header
m_objId / m_indexId	The object and index that own the page
m_lsn	Log Sequence Number of the last change (ties the page to the .ldf)
m_slotCnt	Number of rows (slots) on the page
m_freeCnt / m_freeData	Free byte count and offset of the free-space start
m_flagBits / torn-bit or checksum	Page-integrity data used to detect corruption

The m_type byte is what lets a single file hold table data, indexes and internal bookkeeping side by side. The m_lsn ties each page to a point in the transaction log, which is how SQL Server rolls a page forward or back during crash recovery: it compares the page’s stored LSN against the .ldf and replays or undoes changes accordingly. This is also why the MDF and its LDF must be kept together; the data file alone can be an inconsistent snapshot mid-transaction.

Extents and the allocation maps: GAM, SGAM, PFS and IAM

Pages are grouped into extents of eight physically contiguous pages, 64 KB each. An extent is either uniform (all eight pages belong to one object) or mixed (pages shared by several small objects). To track which extents and pages are in use, SQL Server reserves special allocation pages near the front of every data file and repeats them at fixed intervals:

Page	Name	Tracks
PFS	Page Free Space	Per-page allocation and how full each page is (one byte per page)
GAM	Global Allocation Map	Which uniform extents are free (one bit per extent)
SGAM	Shared Global Allocation Map	Which mixed extents have free pages
IAM	Index Allocation Map	Which extents an individual table or index owns

A single GAM page maps about 64,000 extents (roughly 4 GB of data), so these maps recur through a large file rather than living only at the start. When SQL Server needs a new page for a table, it consults the table’s IAM to find its extents, then the PFS to find a page with room, updating the GAM/SGAM bitmaps as extents are allocated. This bitmap allocation is what makes growth and space reuse fast without scanning the whole file.

The file header page and the boot page

Two pages have fixed, reserved roles in the primary MDF. Page 0 is the file header page, which records file-level metadata: the file’s size, growth increment, sector size and internal identifiers. Page 9 is the database boot page, which holds database-wide metadata such as the current version, the database creation timestamp, the compatibility level, and the checkpoint LSN that recovery starts from. The boot page exists only in the primary data file, which is one of the things that distinguishes an MDF from a secondary NDF: the NDF shares the page format but not the database-level boot record. When SQL Server attaches an MDF, reading page 9 is how it learns whether the file is a compatible, consistent database before it brings it online.

Anatomy of a data row on a page

Within a data page, each row is stored in a compact record structure that SQL Server parses field by field. A fixed-length-column-first layout keeps the offsets predictable:

row record
  2 bytes   status bits (row type, has variable columns, has NULL bitmap)
  2 bytes   offset to end of fixed-length columns
  N bytes   fixed-length column values
  2 bytes   number of columns
  ceil(cols/8)  NULL bitmap (one bit per column)
  2 bytes   count of variable-length columns
  2 bytes each  variable-column offset array
  ...       variable-length column values

The fixed-length columns come first at known positions, then a NULL bitmap marks which columns are NULL (so a NULL stores no value), and finally the variable-length columns are laid out with an offset array so the engine can find each one. The 2-byte slot entry at the end of the page points to the start of this record. Reading a row therefore means: follow the slot to the record, read the status bits, walk the fixed columns, consult the NULL bitmap, then use the variable-column offsets for the rest. If a variable column is too large to fit, its value moves to a row-overflow or LOB page and the in-row record keeps a pointer instead.

Encryption and page integrity at rest

An MDF often holds business-critical and personal data, so SQL Server can encrypt the whole file at rest with Transparent Data Encryption (TDE). TDE encrypts data and log pages as they are written to disk and decrypts them as they are read into memory, using a database encryption key protected by a certificate in the server’s master database; applications and queries see no change. The practical security consequence is real: if TDE keys are lost, the MDF is unrecoverable ciphertext, so certificate backup is as important as the data backup. Separately, SQL Server can store a checksum in each page header and verify it on read, so silent disk corruption of an MDF page is detected rather than served as valid data. Both features act at the page level described above, which is why they are transparent to the SQL that runs on top.

Other formats that use the .mdf extension

The .mdf extension is reused by unrelated formats, so context matters. An Alcohol 120% or DAEMON Tools disc image is also called .mdf (a “Media Descriptor File”) and appears next to a companion .mds sidecar; it is a CD/DVD image, not a database, and mounts with disc-image tools. The ASAM Measurement Data Format uses .mdf (MDF3) for automotive sensor and ECU logs, read with tools such as asammdf. If a file called .mdf has an .mds beside it, or comes from a car-measurement toolchain, it is one of those formats rather than a SQL Server data file.

FAQ

How do I open an MDF file?

If it is a SQL Server data file, you attach it to a SQL Server instance rather than opening it as a document: use SSMS (Databases → Attach) or the CREATE DATABASE … FOR ATTACH statement, and keep the matching .ldf log file with it. Only then can you query the tables inside.

Why does an MDF need the LDF file?

The LDF is the transaction log. Each page in the MDF records the Log Sequence Number of its last change, and recovery replays or rolls back log records to make the data consistent. Without the LDF, a database that was not cleanly shut down can be left in an inconsistent state.

Why is a single row limited to 8060 bytes?

A page is 8192 bytes, minus the 96-byte header and the slot array, leaving about 8060 bytes for one in-row record. Columns that would exceed that are moved to row-overflow or LOB pages, with a pointer left in the main row.

References

• Microsoft Learn — Pages and Extents Architecture Guide (SQL Server)
• Microsoft Learn — Database Files and Filegroups (MDF/NDF/LDF)
• Microsoft Learn — Transparent Data Encryption (TDE)

Feedback

Was this page helpful?

Yes No

Glad to hear it! Please tell us how we can improve.

Sorry to hear that. Please tell us how we can improve.