Pages

Tuesday, August 9, 2011

What are MDF, NDF and LDF ?

If you have experience with SQL Server, you may have heard the terms MDF, NDF and LDF already. Those are the commonly used file name extensions in SQL Server for specify the Primary Data files, Secondary data files and Log files respectively.

In SQL Server, data and log information are never stored in the same file. Furthermore, those individual files (primary data, secondary data and log) are used by only one server. However, below is a brief description about the three file types in SQL Server.


Primary data files
Primary data file is the starting point of the database. It points to the other files in the database. Therefore, every database has one primary data file. Also, all the data in the database objects (tables, stored procedures, views, triggers.. etc.) are stored in the primary data files. The recommended and the most common file name extension for primary data files is .mdf.

Secondary data files
You can only have one primary data file for a database. Rest made up by secondary data files. But its not necessary to have a secondary data file. Therefore some databases may not have any secondary data file. But its also possible to have multiple secondary data files for a single database. .ndf is usually recommended to denote secondary data files. It’s also possible to store the secondary data file in a separate physical drive than the one which primary data file is stored.

Log files
Log files in SQL Server databases hold all the log information. Those information can be later used to recover the database. Size of the log file is determined by the logging level you have set up on the database.  There must be at least one log file for each database. But it is also possible to have more than one log file for a single database. The recommended file name extension for log files is .ldf.


IMPORTANT : Though it’s recommended to use these .mdf, .ndf and .ldf filenames, ASQL Server never enforces to use those file extensions.

Locations of all the files in a database (primary, secondary and log files) are stored in the primary file of the database and in the master database. When the SQL Server Database Engine want to use the file location information of those  files, it retrieves the data from the master database.

20 comments:

  1. good article thanks .. :)

    ReplyDelete
  2. I agree. This is a great, short identification of what the file naming convention is.

    ReplyDelete
  3. Thanks for your article

    ReplyDelete
  4. I have a doubt what ndf file don't contain that mdf file contains?

    ReplyDelete
  5. I just know there is ndf extension for secondary data files.

    ReplyDelete
  6. Nice Article, but what are the importance of .mdf file and .ldf file is not discussed after a long search I found a nice written article including all the terms like what are the importance of .mdf and .ldf files and the reasons of corruption of these files, here also discussed about the symptoms of corrupted MDF and LDF files. http://sqltechtips.blogspot.in/2015/11/introduction-mdf-ldf.html

    ReplyDelete
  7. Nice Article !

    Really this will help to people of SQL Server Community.
    I have also prepared small note on this, Basic storage file structure of the SQL Server.

    http://www.dbrnd.com/2016/04/sql-server-the-internal-storage-architecture-of-the-database-files/

    ReplyDelete
  8. Thanks a lot :)

    ReplyDelete

Had to enable word verification due to number of spam comments received. Sorry for the inconvenience caused.