When we talk about any database on a SQL server, two sorts of files are created on the disc: data files and log files. The data is physically stored on data pages in a data file. Log files, often known as write-ahead logs, are used to record database transactions.
There are three types of files in SQL Server databases:
• Primary data files
• Secondary data files
• Transaction Log files
Data File: Data Files store data in the form of Data Pages (8KB), which are logically organized in extents.
Log File: It is also known as a write-ahead log. It saves database changes (DML and DDL).
Sufficient information is logged to be able to:
• Transactions can be rolled back if necessary.
• In case of failure, recover the database.
• Plan ahead of time Logging is the process of creating log entries.
• Transaction logs are written in a cyclic fashion in chronological order.
• The log truncation policy is based on the recovery model.
Files are classified into three types: primary (.mdf), secondary (.ndf), and log (.ldf).
There are two file groups: Primary File Group – All system tables and User Defined – Depends.
All secondary files and user-defined file groups are created to optimize data access and partition tables.