SQL Server Database Files

Introduction

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],
       physical_name
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.
status	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.