Offline Databases and Orphaned Data Files in SQL Server: How to Find and Remove Them Safely

In SQL Server environments that have been running for some time, particularly development and test servers, two kinds of leftovers tend to accumulate: databases that sit offline even though nobody uses them, and data and log files that remain on disk without belonging to any database. These leftovers consume disk space, distort capacity planning and often represent an overlooked data security risk.

In this guide, we explain how offline databases and orphaned files come about, how to identify them and how to clean them up without risking data loss.

What Is an Orphaned Data File and How Does It Occur?

An orphaned file is a data (.mdf, .ndf) or log (.ldf) file that exists on disk but isn’t used by any database attached to a SQL Server instance. Such files usually appear in the following situations:

• Dropping a database while it is offline. According to Microsoft’s documentation, if a database or any one of its files is offline when DROP DATABASE runs, the database is removed from the instance but the files on disk are not deleted. Because taking a database offline and waiting a while before dropping it is a common habit, this is the most frequent cause of orphaned files.
• Detached databases. sp_detach_db, or the Detach option in SSMS, removes the database from the instance but deliberately leaves its files on disk. Databases that are never reattached leave their files behind in this way.
• Moves and restores. When a database is restored or moved to a different folder, the files in the old location, or manual copies taken before the operation, are often forgotten.
• Uninstalled instances. When a SQL Server instance is uninstalled, the files of its user databases can remain on disk.

Why It Matters

Offline databases and orphaned files may look like nothing more than a disk space issue at first glance, but their impact is broader:

• Disk space and capacity planning. Files left behind by large databases can occupy tens or even hundreds of gigabytes, which leads to misread disk space alerts and unnecessary storage investments.
• Data security. An orphaned .mdf file still contains the data of the dropped database in full. If it is copied to another server and attached, none of the permissions defined in SQL Server protect it. For systems that hold personal data, this risk shouldn’t be overlooked from a data protection standpoint either. We cover how to bring such files into your data inventory and disposal process in our data governance guide.
• Operational noise. Offline databases can cause errors or unnecessary alerts in maintenance and monitoring scripts that iterate over all databases, and they make systems that are no longer in use appear active in inventory reports.

Finding Offline Databases

The following query lists the offline databases on an instance together with their file paths, sizes and most recent full backup date. The last backup date gives an indication of how long the database has been out of use, and shows whether a recent backup exists before cleanup begins:

SELECT d.name                                   AS database_name,
       SUSER_SNAME(d.owner_sid)                 AS owner_name,
       mf.type_desc                             AS file_type,
       mf.physical_name,
       CAST(mf.size AS bigint) * 8 / 1024       AS size_mb,
       (SELECT MAX(b.backup_finish_date)
          FROM msdb.dbo.backupset AS b
         WHERE b.database_name = d.name
           AND b.type = 'D')                    AS last_full_backup
FROM sys.databases AS d
JOIN sys.master_files AS mf
     ON mf.database_id = d.database_id
WHERE d.state_desc = N'OFFLINE'
ORDER BY d.name, mf.type_desc;

For each database in this list, the question to answer is why it was taken offline and whether anyone still needs it. Usually, only the application owner can answer that question.

Finding Orphaned Files

The reliable way to identify orphaned files is to collect the list of files used by every SQL Server instance on the server and compare it with the files in those folders. Looking at a single instance can be misleading: if the server hosts several instances, a file belonging to one of them will appear “orphaned” from the perspective of another.

The following PowerShell script uses Invoke-Sqlcmd from the SqlServer module to collect the file list and the default data and log folders for the instances you specify, and then lists the data and log files in those folders that don’t belong to any instance:

# List ALL instances on this server
$instances = @('SQLHOST', 'SQLHOST\INST2')

$known   = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)
$folders = New-Object 'System.Collections.Generic.HashSet[string]' ([StringComparer]::OrdinalIgnoreCase)

foreach ($i in $instances) {
    Invoke-Sqlcmd -ServerInstance $i -Query "SELECT physical_name FROM sys.master_files;" |
        ForEach-Object {
            [void]$known.Add($_.physical_name)
            [void]$folders.Add((Split-Path $_.physical_name -Parent))
        }
    Invoke-Sqlcmd -ServerInstance $i -Query "SELECT CAST(SERVERPROPERTY('InstanceDefaultDataPath') AS nvarchar(260)) AS p
                                             UNION
                                             SELECT CAST(SERVERPROPERTY('InstanceDefaultLogPath') AS nvarchar(260));" |
        ForEach-Object { [void]$folders.Add($_.p.TrimEnd('\')) }
}

Get-ChildItem -Path @($folders) -File -ErrorAction SilentlyContinue |
    Where-Object { $_.Extension -in '.mdf', '.ndf', '.ldf' -and -not $known.Contains($_.FullName) } |
    Select-Object FullName,
                  @{ n = 'SizeGB'; e = { [math]::Round($_.Length / 1GB, 2) } },
                  LastWriteTime |
    Sort-Object SizeGB -Descending

The script only looks in folders that SQL Server knows about. If you also want to find files copied to other folders, you can add those paths to the $folders list manually. The Find-DbaOrphanedFile command in the open-source dbatools module can also be used for a similar check.

A Safe Cleanup Process

For offline databases

Before removing an offline database that has been confirmed as unused, we recommend taking a final backup so that you can return to it if needed, and dropping the database while it is online:

1. Confirm in writing with the application owner that the database is no longer in use.
2. Bring the database online.
3. Take a final full backup with checksum and verify that the backup is readable.
4. Keep this backup for the period defined in your organization’s retention policy.
5. Drop the database while it is online, so that the physical files are removed as well.

ALTER DATABASE [OldSalesDB] SET ONLINE;

BACKUP DATABASE [OldSalesDB]
  TO DISK = N'backup-sharearchiveOldSalesDB_final.bak'
  WITH CHECKSUM, COMPRESSION;

RESTORE VERIFYONLY
  FROM DISK = N'backup-sharearchiveOldSalesDB_final.bak'
  WITH CHECKSUM;

DROP DATABASE [OldSalesDB];

A database that cannot be brought online because one of its files is missing can still be dropped, but in that case the remaining files have to be removed manually afterwards.

For orphaned files

For files that don’t belong to any instance, we recommend a staged approach rather than immediate deletion:

1. Confirm that the file isn’t used by any instance on the server and isn’t a copy that is being kept on purpose.
2. If you’re unsure what the file contains, attach it to a test instance with CREATE DATABASE ... FOR ATTACH and inspect it there.
3. Rather than deleting the files straight away, move them to a quarantine folder with restricted access and wait for a predefined period, for example 30 days.
4. If no need arises during that period, delete the files permanently and record the action.

Preventing the Problem at the Source

Most orphaned files result from the absence of a defined procedure for removing databases. Following the same steps every time a database is decommissioned, namely taking and verifying a final backup and dropping the database while it is online, largely prevents the problem from recurring. Rather than leaving databases offline for long periods, backing them up and removing them once the decision has been made is both cleaner and safer. We also recommend adding the offline database and orphaned file checks to your monthly maintenance routine.