NDF File Format

What is a NDF file?

A file wiht .ndf extension is a secondary database file used by Microsoft SQL Server to store user data. NDF is secondary storage file because SQL server stores user specified data in primary storage file known as MDF. NDF data file is optional and is user-defined to manage data storage in case the primary MDF file uses all the allocated space. It is usually stored on separate disk and can spread to multiple storage devices. The presence of MDF files is necessary in order to open NDF files.

NDF file format is no different than MDF and uses pages as the fundamental unit of data storage. each page starts with 96 bytes header that includes:

Page ID
Type of Structure
Number of records in the pages
Pointers to previous and next pages

NDF File Structure

A MDF file has the following data structure.

Page 0: Header
Page 1: First PFS
Page 2: First GAM
Page 3: First SGAM
Page 4: Unused
Page 5: Unused
Page 6: First DCM
Page 7: First BCM

File Header

The page number 0 of all the files contains a header that stores metadata about the file.

Page Free Space (PFS)

PFS identifies the allocation status and determines the amount of free space.

Bit 1: Indicates whether the page is allocated or not.
Bit 2: Indicates if the page is from a mixed extent.
Bit 3: Indicates that this page is an IAM page.
Bit 4: Indicates that this page contains ghost records
Bits 5 to 7: A combined three-bit value, which indicate the page fullness as follows:
- 0: The page is empty
- 1: The page is 1–50% full
- 2: The page is 51–80% full
- 3: The page is 81–95% full
- 4: The page is 96–100% full

Data File Page

Pages in a SQL Server data file start from zero (0) and increment sequentially. Each file is recognized by a unique file ID number. The file ID and page number pair uniquely identifies a page in a database. An example showing page numbers in a database, is as in following image.

This example shows page numbers in a database that has a 4-MB primary data file and 1-MB secondary data file.