SQL Server database is a logical collection of data. In SQL Server, data is stored in two set of files, Data files and Log files. When we create a database there are two files created for every database that is the Data File (.mdf) and Log File(.ldf) and a Primary filegroup (a container) is created in which .mdf (Primary Data File) resides. Log file cannot be contained in a Filegroup. It is always recommended to store the Log File in a different disk separate from data file. Maximum size of the database in Enterprise Edition can be 524 Petabytes. 1 Petabyte is equal to 1024 TB and 1 TB is equal to 1024 GB.
DATA files: –
- Data files in SQL server stores the actual data.
- We can create multiple data files for a particular database.
- Maximum size of each data files is said to be 16 TB.
Database has two different types of data files as s shown in the image.
1.MDF (Master Data File): –
- This is a default data file, which is created when a database is created.
- We cannot delete this file, also we cannot create more than one MDF file, there can be one and only MDF file per database.
- Database will not be available without MDF file
- This file stores user data along with some Metadata (data about data) about the database configurations like collation, recovery model, user’s information and roles etc.
2.NDF (User Data File): –
- This is a secondary data file. We can create multiple ndf files with any extension.
- Data files can be grouped with filegroups.
LOG files: –
- This file stores all the transactional logs in sequential order, each transaction will be given a unique number called LSN (Log Sequential Number).
- We can create multiple log files per database.
- Log files cannot be grouped under file groups.
- The maximum size of each log file is 2 TB.