SQL Server - What is MDF, NDF, LDF?

If you are working on SQL related concepts, then there would be high chances of you coming across .mdf, .ndf and .ldf files. These are files nothing but primary data files, secondary data files, and logical files.

Primary Data File

The Primary data file is the .mdf file, where all the objects such as tables, views, triggers, stored procs, functions, etc., are stored. There is always just one .mdf file for a database. Further, every database should consist of a starting point and this is handled by the primary file. The primary file is held responsible for pointing the other files of a database. .mdf files are stored in the physical storage drive of the computer.

Example: <database_name>.mdf

Secondary Data File

A secondary data file is held responsible to hold all the data apart from the one handled by the primary data file. There are places where no secondary data files are present for a database. However, the majority of times, there are two secondary data files present in a database. Similar to primary files, the secondary files are as well stored in the physical storage drive of the computer.

Example: <database_name>.ndf

Log File

The log files hold the logical information of a database. It helps in recovering the database. A database can have one or more log files. The log files as well are stored in the local machine. Usually, log file sizes allotted in either a physical drive or mount drive can increase due to an increased transaction in the database. It can further be shrunk with special queries.

Example: <database_name>.ldf

Related interesting article: