Test restores, detached databases, and failed drops can leave .mdf, .ndf, and .ldf files in SQL Server data folders. Orphaned data and log files can fill a drive while no attached database reports their size. A folder inventory compared with sys.master_files finds candidates for investigation.
Treat Orphaned Data and Log Files as Candidates, Not Proof
A file absent from sys.master_files is not automatically safe to delete. It can belong to a detached database awaiting reattach, a recovery copy, a planned migration, another SQL Server instance, or a process outside the current instance. The query below produces candidates. The cleanup decision needs ownership and backup evidence.
I start with a disk-space alert and a list of database files the instance knows. What is the oldest unclaimed file, and who created it? A filename such as Sales_Test_2.mdf is a clue, not an authorization to remove data.
List the Attached File Paths
sys.master_files shows physical paths for files belonging to databases on the current instance. Its physical_name is the authoritative comparison set for this instance. Extract the parent folders so the next query can enumerate each data directory without guessing one default path. This first pass is read-only.
SELECT DB_NAME(database_id) AS database_name,
name AS logical_name, type_desc, state_desc,
physical_name, size * 8.0 / 1024 AS catalog_size_mb
FROM sys.master_files
ORDER BY physical_name;The catalog size is allocated file size in 8 KB pages, not the file's current Windows length in every unusual state. A database can span several volumes. Include all its folders, and record mounted volume paths or UNC paths where used. The SQL Server service account must be able to enumerate each location.
Enumerate the Folders From SQL Server
On SQL Server 2017 and later, sys.dm_os_enumerate_filesystem can return file names, full paths, sizes, and timestamps for a folder. The function is not documented as a stable public API in the same way as the catalog view, so verify availability and columns on the target build. It reads the SQL Server host's filesystem, not the SSMS workstation's drive.
DECLARE @folder nvarchar(4000) = N'D:\SQLData';
SELECT full_filesystem_path, file_or_directory_name,
size_in_bytes / 1048576.0 AS size_mb,
creation_time, last_write_time
FROM sys.dm_os_enumerate_filesystem(@folder, N'*')
WHERE is_directory = 0
AND RIGHT(LOWER(file_or_directory_name), 4)
IN (N'.mdf', N'.ndf', N'.ldf');Test one folder first. Access denied or an empty result needs investigation; it does not prove the folder contains no files. Do not enable xp_cmdshell as a workaround for this read-only inventory. A local PowerShell inventory can be a supported alternate method if the DMF is unavailable.
Find Orphaned Data and Log Files Across Known Folders
The following query derives distinct parent folders from sys.master_files, enumerates matching extensions, and excludes paths registered to the instance. Review path casing and mount-point behavior on your Windows configuration. Restrict the result to files, not subfolders, and sort large candidates first.
WITH folders AS
(
SELECT DISTINCT LEFT(physical_name,
LEN(physical_name) -
CHARINDEX(N'\', REVERSE(physical_name))) AS folder_path
FROM sys.master_files
WHERE CHARINDEX(N'\', REVERSE(physical_name)) > 0
), files AS
(
SELECT f.full_filesystem_path,
f.file_or_directory_name, f.size_in_bytes,
f.creation_time, f.last_write_time
FROM folders AS d
CROSS APPLY sys.dm_os_enumerate_filesystem
(d.folder_path, N'*') AS f
WHERE f.is_directory = 0
AND RIGHT(LOWER(f.file_or_directory_name), 4)
IN (N'.mdf', N'.ndf', N'.ldf')
)
SELECT files.full_filesystem_path,
files.size_in_bytes / 1048576.0 AS size_mb,
files.creation_time, files.last_write_time
FROM files
WHERE NOT EXISTS
(
SELECT 1 FROM sys.master_files AS mf
WHERE mf.physical_name = files.full_filesystem_path
)
ORDER BY files.size_in_bytes DESC;On my SQL Server 2025 test instance, the only candidates were model_msdbdata, model_msdblog, and model_replicatedmaster files in the default data folder. SQL Server ships those template files with the instance, so they are not orphaned data and log files. This query scans folders already represented in sys.master_files. It will miss a detached database stored in a folder with no current attached file. Add explicitly approved data folders to the inventory for full coverage. The query also sees only the current instance's catalog; another instance using the same folder needs its own comparison.
Review Each Candidate Before Cleanup
For every candidate, check restore tickets, change records, backup history, file creation and modification dates, and whether another service has the file open. Confirm the database owner and retention requirement. Make a verified backup or move the file into a quarantined, access-controlled location under an approved process before permanent deletion. Never automate Remove-Item from this query's output.
I compare the file header where appropriate on a test machine and record its database identity. I do not attach an unknown file to production just to inspect it. A recent .ldf beside an older .mdf can be part of an incomplete restore. Keep pairs together until the owner confirms their purpose.
Close the Capacity Investigation
After approved cleanup, recheck Windows free space and rerun the inventory. Keep a record of candidate path, size, owner, decision, and recovery location. A recurring report can detect new unclaimed files early, but it should stay read-only and send a review list.
If candidates grow after every test restore, fix the teardown process rather than treating weekly deletion as maintenance. The valuable result is a clear inventory and a controlled decision. The anti-join narrows the search; human ownership and recovery evidence make the final cleanup safe.
Account for Folders With No Attached File
The automatically derived folder list has a blind spot: a directory created solely for a database that was detached or dropped will have no current path in sys.master_files. Add known SQL data directories from server configuration, restore automation, and storage inventory to the review scope. Do not recursively scan an entire drive without a reason; it can be slow and can expose unrelated files. A bounded directory list is easier to review and schedule.
Compare Paths Carefully
Windows paths can differ in case, mount-point notation, or a trailing separator while identifying the same file. The query uses a direct string comparison because that is easy to audit, but investigate suspicious near-matches before labeling them orphaned. A path used by a different SQL Server instance will also be absent from this instance's catalog. Ask the Windows or platform team which services share the volume.
The timestamp fields are filesystem metadata, not a reliable last-used date for database activity. A copied file can have preserved timestamps, and a detached file can be recent but still required. I use size and date to prioritize review, never as a deletion rule.
Clean Up Orphaned Data and Log Files Apart From Discovery
Export a candidate list with a unique case ID and ask the owner to classify each file as active elsewhere, retained backup, temporary copy, or unknown. For approved temporary copies, record the path and backup location before moving or deleting them with native Windows tools. Recheck free space and the file list afterward. If ownership cannot be established, leave the file and escalate the storage request rather than guessing from its extension.
A recurring read-only inventory can show new candidates before the volume fills. Pair it with alerts on drive free space and restore-job cleanup failures. The goal is to prevent abandoned files from accumulating, not to make an unattended deletion job.
Related reading on this blog: How to Get Details of All Files Associated with Database from MDF? Interview Question of the Week #275 and Find the Growth Size for All files in All Databases: Part 2.
An unregistered file is not trash, it is an item to investigate before any deletion.
Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.
