What is Logical files in MSSQL Server

Microsoft SQL Server consists of logical and physical files that store and manage database data.
Logical files refer to the representation of physical files in SQL Server.

Logical File Types in SQL Server

1. Primary Data File (MDF):
   • The main database file.
   • Contains the database schema and data.
   • Every database has exactly one primary data file.
   • Logical name typically corresponds to the database name (e.g., MyDatabase).
2. Secondary Data File (NDF):
   • Optional file used for additional data storage.
   • Useful for large databases or when spanning multiple disks.
   • A database can have zero or more secondary data files.
   • Logical name varies, often used to describe purpose or location.
3. Transaction Log File (LDF):
   • Stores all transaction log information for database recovery.
   • Essential for maintaining ACID properties.
   • Every database must have at least one log file.

Logical vs. Physical Files

Key Operations with Logical Files

1) Defining Logical Files

Logical files are specified during database creation:

CREATE DATABASE MyDatabase
ON 
(NAME = MyDatabase_Data, FILENAME = 'C:\SQLData\MyDatabase.mdf', SIZE = 10MB)
LOG ON 
(NAME = MyDatabase_Log, FILENAME = 'C:\SQLLogs\MyDatabase.ldf', SIZE = 5MB);

2) Querying Logical Files

View logical and physical file mappings with:

SELECT 
    name AS LogicalName, 
    physical_name AS PhysicalName, 
    type_desc AS FileType 
FROM sys.master_files 
WHERE database_id = DB_ID('MyDatabase');

3) Modifying Logical Files

Modify properties such as size or growth:

ALTER DATABASE MyDatabase
MODIFY FILE (NAME = MyDatabase_Data, SIZE = 50MB);

4) Adding Logical Files

Add a new file to an existing database:

ALTER DATABASE MyDatabase
ADD FILE 
(NAME = MyDatabase_AdditionalData, FILENAME = 'C:\SQLData\MyDatabase_Additional.ndf', SIZE = 20MB);

5) Removing Logical Files

Files can only be removed if they are empty:

ALTER DATABASE MyDatabase
REMOVE FILE MyDatabase_AdditionalData;

Best Practices

• Logical Names: Use meaningful names for easier maintenance.
• Filegroup Organization: Use filegroups for performance and organization.
• Monitor File Growth: Set auto-growth settings and monitor disk space.