Types of database files
An SQL Server database consists of two or more files:
Physical and logical filenames
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):
If the systems fails the automatic recovery process rollbacks all incomplete transactions and commits all complete transactions.