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.


DatabaseFil1.jpg
Select "Properties" > "Files".

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

DatabaseFil2.jpg

Now click the "Add" button.

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

Then click the "OK" button.

DatabaseFil3.jpg

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%'
-----------------------------------------------------------------

DatabaseFil4.jpg

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
-----------------------------------------------------------------

DatabaseFil5.jpg

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.