SQL SERVER
DATABASE
FILES
(IN DETAIL)
LEARNING OBJECTIVES
• • Identify types of SQL Server files
(.mdf, .ndf, .ldf)
• • Understand filegroups and their roles
• • Learn to manage file sizes and growth
settings
• • Explore best practices for file placement and
performance
INTRODUCTION TO DATABASE
FILES
• • Every SQL Server database consists of one or
more files.
• • Each file stores specific types of information
(data or logs).
• • Files form the physical structure of a SQL
Server database.
TYPES OF SQL SERVER FILES
• 1. Primary Data File (.mdf) – Contains system
tables and startup info.
• 2. Secondary Data Files (.ndf) – Optional files
for additional storage.
• 3. Transaction Log Files (.ldf) – Records all
database changes.
PRIMARY DATA FILE (.MDF)
• • Main file of every database.
• • Stores system tables and data.
• • Points to other files in the database.
• • Each database must have exactly one .mdf
file.
• Example Path: C:\SQLData\[Link]
DEMO: CREATING A DATABASE
• CREATE DATABASE SalesDB
• ON PRIMARY (
• NAME = SalesDB_Data,
• FILENAME = 'C:\SQLData\[Link]',
• SIZE = 50MB, MAXSIZE = 500MB, FILEGROWTH = 10MB
• ) LOG ON (
• NAME = SalesDB_Log,
• FILENAME = 'C:\SQLLogs\[Link]',
• SIZE = 20MB, FILEGROWTH = 5MB );
SECONDARY DATA FILE (.NDF)
• • Used for large databases to spread data
across disks.
• • Belongs to a user-defined filegroup.
• • Improves performance and scalability.
• • Optional but beneficial in large environments.
TRANSACTION LOG FILE (.LDF)
• • Stores all transactions and modifications.
• • Ensures data recovery and rollback capability.
• • Multiple log files can exist, but only one active
at a time.
• • Should be placed on a separate disk for
performance.
UNDERSTANDING FILEGROUPS
• • Logical container for data files.
• • Types:
• - Primary Filegroup (default)
• - User-defined Filegroups (custom)
• • Transaction log is not part of any filegroup.
EXAMPLE: CREATING A
FILEGROUP
• ALTER DATABASE SalesDB ADD FILEGROUP
FG_Sales2025;
• ALTER DATABASE SalesDB ADD FILE (
• NAME = Sales2025_Data,
• FILENAME = 'E:\SQLData\[Link]',
• SIZE = 200MB
• ) TO FILEGROUP FG_Sales2025;
FILE SIZE MANAGEMENT
• • Each file has SIZE, MAXSIZE, and
FILEGROWTH attributes.
• • Example:
• ALTER DATABASE SalesDB MODIFY FILE (
• NAME = SalesDB_Data, FILEGROWTH =
20MB );
• • Avoid frequent auto-growth; pre-size
databases appropriately.
VIEWING FILE INFORMATION
• USE SalesDB; EXEC sp_helpfile;
• Or view all databases:
• SELECT [Link], [Link], mf.physical_name,
mf.type_desc,
• [Link]*8/1024 AS SizeMB
• FROM sys.master_files mf
• JOIN [Link] db ON mf.database_id =
db.database_id;
BEST PRACTICES
• • Separate data and log files on different
drives.
• • Use fixed growth increments instead of
percentages.
• • Monitor file size and auto-growth events.
• • Include log backups for full recovery model.
• • Use multiple data files for large databases or
TempDB.
SUMMARY
• • .mdf – Primary data file (mandatory).
• • .ndf – Secondary data file (optional).
• • .ldf – Transaction log file (mandatory).
• • Filegroups organize data files logically.
• • Proper sizing and placement boost
performance.
Q&A / DISCUSSION
• • Why is the transaction log critical for
recovery?
• • How many log files can a database have?
• • What are the benefits of multiple filegroups?
THANK YOU
• BY BALAJI.R