User Tools

Site Tools


Physical Database Structure

Types of database files

An SQL Server database consists of two or more files:

  • .MDF - primary files (one and only one)
  • .NDF - secondary files (optional)
  • .LDF - log files (at least one)

Physical and logical filenames

  • A physical filename - the name of a physical file on the hard drive.
  • A logical filename - a filename that is used in the SQL statements.

File properties

  • initial size
  • maximum size
  • automatic growth
  • growth increment

Extents Extents are the building blocks from which tables and indexes are constructed.

1 Extent = 8 contiguous pages = 64 KB

Rows A row of data cannot span multiple pages, so it has the same size as the page (8KB) minus 96 bytes for the row header i.e. 8192 - 96 = 8096.

Transaction log Transaction log records data modifications as they are executed (UPDATE, INSERT, DELETE). By default the size of the log file is 25% of the data file.

How the transaction log works (write-ahead log):

  • An application sends data modification
  • The affected data pages are loaded from disk in memory. The memory is called buffer cache.
  • The transaction log records the data modification on disk.
  • The data modification is executed.
  • The checkpoint process writes all completed transactions to the database on the disk.
  • The transaction log is cleared automatically when SQL Server performs a checkpoint

If the systems fails the automatic recovery process rollbacks all incomplete transactions and commits all complete transactions.

notes/sql/physical_database_structure.txt · Last modified: 2020/08/26 (external edit)