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 obje...