SQL Server stores recovery information in the LDF file, also called the transaction log file. Every database has at least one. MDF files are primary data files and NDF files are optional secondary data files. Query sys.master_files to see the exact location of every file on an instance.
You find a file ending in LDF next to your database and need to know whether it is safe to move, copy or delete. Start with what the file does.
This guide follows Microsoft documentation and shows how to find file locations with a query.
What are the SQL Server Database File Types?
| File | Extension | Purpose |
|---|---|---|
| Primary data file | .mdf | Stores startup information and points to the other files. Every database has one |
| Secondary data file | .ndf | Optional user defined file that holds data and objects |
| Transaction log file | .ldf | Stores the information needed to recover the database |
See database files and filegroups for the full description. Microsoft also states that transaction log files are never part of any filegroup.
What does the LDF File Do?
Transaction log records live in the LDF file. SQL Server uses them to recover the database after a failure. Both files matter for recovery.
Do not delete an LDF file to save space. Microsoft documents the log as part of recovery and removing it risks the database. For space problems create a log backup or review the recovery model.
Where is the LDF File Stored?
Microsoft notes that data files and log files sit on the same disk by default. Folders differ by version, instance name and setup choices. Queries give safer answers than fixed paths. System view sys.master_files returns one row per database file. See sys.master_files.
-- List every data and log file on the instance
SELECT DB_NAME(database_id) AS database_name,
name AS logical_name,
type_desc,
physical_name
FROM sys.master_files
ORDER BY database_id, file_id;The type_desc column shows ROWS for data files and LOG for transaction log files. You need CREATE DATABASE, ALTER ANY DATABASE or VIEW ANY DEFINITION permission to see the rows.
Where should You Place Log Files?
Microsoft states that placing data files and log files on the same disk suits single disk systems but is not always optimal for production environments. It recommends separate disks for data and log files.
For recovery planning see SQL Server disaster recovery options. If a database fails to open see how to fix a SQL Server database in suspect mode.
Frequently Asked Questions
What is an LDF File?
LDF is the SQL Server transaction log file. It stores the information needed to recover the database.
Where is the LDF File Located?
Query sys.master_files and check the physical_name column. Folders depend on version and instance.
Can I Delete an LDF File?
No. Microsoft documents the log as part of recovery and deleting it risks the database.
What is the Difference Between MDF and LDF?
MDF is the primary data file and LDF is the transaction log file.
Should the LDF File Sit on a Separate Disk?
For production systems Microsoft recommends separate disks.
Key Takeaways
- LDF is the SQL Server transaction log file.
- MDF is the primary data file and NDF files are optional.
- Query sys.master_files to find every file location.
- Deleting an LDF file risks the database.
- Microsoft recommends separate disks for data and log files.
Sources and References
This guide follows Microsoft documentation.
- Database Files and Filegroups (Microsoft Learn) learn.microsoft.com
- sys.master_files (Microsoft Learn) learn.microsoft.com