The Role of MDF and LDF in SQL SERVER

Now a day all the companies need a system to store data and again they can retrieve it for their own purpose. The Information can be anything from customer related, seller related, market research, inventory of supplies etc.  But, where do companies store that data and how do they retrieve it? Well, they use databases for that purpose.

Most of the companies use MS SQL Server for storing and managing information. That makes Microsoft SQL Server one of the most used programs globally. Within each database, you will find two files namely; MDF and LDF. The two happens to be very crucial in ensuring uninterrupted functioning of the database.

These two are basically file extensions used in Microsoft SQL. These files get automatically created at the time of database creation. They also share the same storage location. The reason why these files are so important is because they happen to be part of backup and recovery process. In simpler words, in case something bad happens to the database, these are the files the administrator will resort to for restoring and recovering the lost/damaged data.

You can check the Location in drive where it has created.

Select * from sys.database_files

==> got to Physical name column .

Information Contained in MDF and LDF files

MDF – It stands for Master Database File. It contains all the main information of the database that are part of the server. This extension also points to various other files. It plays a crucial role in information storage. Overall it is very important for safe and secure supervision of data. In case this file gets damaged, an MDF recovery procedure is conducted to recover it. Doing so is important in order to save the data from going missing.

LDF – This file stores information related to transaction logs for main data file. It basically keeps track of what all changes have been made in the database. The information that this file stores ranges from date/time of change, details of the changes made, as well as information related to whoever made the changes. Information related to computer terminals where changes took place is also stored in the logs.

LDF stores changes related to inserts, deletion, updates, addition, etc. Transaction logs kept in the server help in identifying activities related to unauthorized changes as well as where an error is originating. Log information can sometimes come handy in fixing errors, recovering important data, and identifying anomalies.

SQL Operations where LDF Files Play an Important Role

Primarily LDF files are important in three major SQL operations:

1.    Recovering incomplete transactions when server is started.

2.    Recovering individual transactions.

3.    Recovering database in times of failures.

Comparison between MDF and LDF Files

1.    MDF file is the primary file in SQL server database. The LDF is a supporting file. The latter stores the information related to transaction logs.

2.    MDF contains database record data. LDF, on the other hand records information related to changes made in the server as well as all the actions performed.

3.    Unlike MDF, LDF is primarily about three major operations that were mentioned earlier.

4.    LDF files can go on to consume a lot of storage space depending on the number of changes made in the server as well as the number of transactions that took place. MDF, on the other hand can vary in its file size with the change of the table and record data.

 NDF (next data file):

An NDF file is a user defined secondary database file of Microsoft SQL Server with an extension .ndf, which store user data. Moreover, when the size of the database file growing automatically from its specified size, you can use .ndf file for extra storage and the .ndf file could be stored on a separate disk drive. Every NDF file uses the same filename as its corresponding MDF file. We cannot open an .ndf file in SQL Server Without attaching its associated .mdf file.