SQL Server Database Files

In this article I describe SQL Server database files. A SQL Server Database is a collection of data objects, it contains 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 that is required to recover all transactions in the database. Data files can be grouped together in file groups for allocation and administration purposes.

Basically 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 remainig 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 be at least one log file, the extension of a log file is LDF.

How to create database files

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

If you want to create additional data files, in other words secondary data files like NDF then 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 name 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 (in 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. Can be either a number of pages or a percentage of file size, depending on value of status.
status	0x2 = Disk file. 0x40 = Log device. 0x80 = File has been written to since 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.