SQL Server Database Files


In this article, I describe SQL Server database files. A SQL Server Database is a collection of data objects containing two operating system files. One is a data file, and another is a log file. Data files consist of data and objects such as tables, indexes, Stored Procedures, and views. Log files contain the information required to recover all transactions in the database. Data files can be grouped in file groups for allocation and administration purposes.

SQL Server allows the following three types of database files.

Primary data files

Every database has one primary data file that contains a track of all the remaining files in the database, in addition to storing data. By agreement, the extension of a primary data file is MDF.

Secondary data files

A secondary database may be zero or more data files. According to requirements, the extension of a secondary data file is NDF.

Log files

Log files hold all the log information to recover the database. Every database must have at least one log file; the extension of a log file is LDF.

How to create database files?

When we create a database, the SQL Server Database contains two operating system files, MDF and LDF.

If you want to create additional data files, such as secondary data files like NDF, we can use the following procedure.

Right-click on the database name.


Select "Properties" > "Files."

We will see the two operating system files, MDF and LDF, as in the following.


Now click the "Add" button.

Now we can define file names like in the following screen.

Then click the "OK" button.


New data files have been created; we can check using the following query.

SELECT name,
       RIGHT(physical_name, 4) AS File_DataType,
       type_desc               AS [DESCRIPTION],
FROM   sys.master_files
WHERE  name LIKE 'testing%'


Database files each have several properties. The properties of each file are noted in the sysfiles that contain one row for each file used by a database.

We can check using the following query.

select * from sysfiles


Column Name Description
fileid The file identification number (unique for each database).
groupid The filegroup identification number.
Size The size of the file (in 8-KB pages).
maxsize The maximum file size (8-KB pages). A value of 0 indicates no growth, and a value of - 1 indicates that the file should grow until the disk is full.
growth The growth size of the database. A value of 0 indicates no growth. It can be either a number of pages or a percentage of file size, depending on the status value.

0x2 = Disk file. 0x40 = Log device. 0x80 =

The file has been written since the last backup. 0x100000 = Growth is in percentage, not pages.

name The logical name of the file.
filename The name of the physical device, including the full path of the file.

Similar Articles