Posts

Microsoft SQL Server Database File Structure (SQL Server 2008 R2)

Note: this article originally appeared as article #46 on ugts.org.  I have since moved it to Blogger since it was the only resource that most people wanted from the website.

A database data file (MDF or NDF) in SQL Server is stored using the format defined by the SQL Server Storage Engine.  Database files use a moderately simple format with a small set of data structures to store all the objects in an SQL Server database.
A database file at its simplest level is an array of 8KB pages - bytes 0x0-0x1FFF in the file belong to page ID = 0.  Bytes 0x2000-0x3FFF belong to page ID = 1, etc...  Page ID values start at 0 and count upward.  Page IDs are 32 bit signed values, meaning that the largest possible database file in SQL server is 2^31 pages * 2^13 bytes per page = 2^44 bytes = 16TB.  This is a limitation that has been present from SQL Server 2000 up to 2008 R2.
Pages are the fundamental unit of allocation for data stored to disk.  Every object and structure is the database has a set o…