Monday, March 5, 2012

SQL Database File Extensions

The database file extensions .mdf and .ldf are quite straightforward.

Data files are organized in logical groups called filegroups (FG). Filegroups are your way to control what will be the physical locations of your objects. A database must have at least one filegroup called PRIMARY, and optionally other user filegroups as well. The PRIMARY filegroup contains the primary data file (extension .mdf), and the database's system catalog.

You can optionally add secondary data files (extension .ndf) to PRIMARY. User filegroups contain only secondary data files. You can determine which of the filegroups is marked as the default filegroup. An object is created on the default filegroup when the object creation statement does not explicitly specify the target filegroup. 

For Example: Database Partition

.ldf stands for Log Data File. Although SQL Server can write to multiple data files in parallel, it can only write to one log file at a time, in a sequential manner. Therefore, unlike with data files, having multiple log files does not result in performance benefit. You might need to add log files if the disk drive where the log resides runs out of space.

Reference: Microsoft® SQL Server® 2008 T-SQL Fundamentals by Itzik Ben-Gan

1 comment: