SQL Server: The internal storage architecture of the Database Files

This is one of the important topic for the internal Storage File Architecture of the SQL Server.

SQL Server is managing two different kinds of files one is for Data and other is for Log information.
The File-groups is a collection of database files and it helps to manage database files.

Type of the SQL Server Database Files.

Primary Data File (.mdf):

The Primary Data File is starting point of the database and also points to the other files.

Each database has only one primary data file and it stores all the tables, indexes, views, functions, and other database objects.
It also contains information about the location of all other files.
The default extension of this data file is .mdf.

Secondary Data File (.ndf):

When database exceeds the maximum allocated size, Secondary Data File helps the database to continue to grow. The extension of this file is .ndf.
This is not mandatory to create this file for each database.
When we are configuring table partitions, we can create separately .ndf file to store partition data.

Log File (.ldf):

This file contains all transaction log information of a database which helps us to recover an entire database in case of any loss.
The Database Transactions are written to the log file even before they are written to the data file.

All the database file can grow automatically from their specified size and if we have not specified default size, it can continue to grow until the disk full.

The Filegroup is a logical container of the database files and the Primary Filegroup is a default Filegroup which contains a primary data and log file of a database.
If there are multiple files in a Filegroup, they will not auto-grow until all the files are full.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of