Find-DbaOrphanedFile

Want to see the source code for this command? Check out Find-DbaOrphanedFile on GitHub.
Want to see the Bill Of Health for this command? Check out Find-DbaOrphanedFile.

Synopsis

Identifies database files on disk that are not attached to any SQL Server database instance

Description

Scans filesystem directories for database files (.mdf, .ldf, .ndf) that exist on disk but are not currently attached to the SQL Server instance. This is essential for cleanup operations after database drops, detaches, or failed restores that leave behind orphaned files consuming disk space.

The command compares files found via xp_dirtree against sys.master_files to identify true orphans. By default, it searches the root\data directory, default data and log paths, system paths, and any directory currently used by attached databases.

Perfect for storage cleanup scenarios where you need to reclaim disk space by identifying leftover database files that can be safely removed. You can specify additional file types using -FileType and additional search paths using -Path parameter.

Syntax

Find-DbaOrphanedFile -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Path <String[]>]
    [-FileType <String[]>]
    [-LocalOnly]
    [-EnableException]
    [-Recurse]
    [<CommonParameters>]

Find-DbaOrphanedFile -SqlInstance <DbaInstanceParameter[]>
    [-SqlCredential <PSCredential>]
    [-Path <String[]>]
    [-FileType <String[]>]
    [-RemoteOnly]
    [-EnableException]
    [-Recurse]
    [<CommonParameters>]

Examples

Example: 1

PS C:\> Find-DbaOrphanedFile -SqlInstance sqlserver2014a

Connects to sqlserver2014a, authenticating with Windows credentials, and searches for orphaned files. Returns server name, local filename, and unc path to file.

Example: 2

PS C:\> Find-DbaOrphanedFile -SqlInstance sqlserver2014a -SqlCredential $cred

Connects to sqlserver2014a, authenticating with SQL Server authentication, and searches for orphaned files. Returns server name, local filename, and unc path to file.

Example: 3

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1', 'E:\Dir2'

Finds the orphaned files in "E:\Dir1" and "E:Dir2" in addition to the default directories.

Example: 4

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -Path 'E:\Dir1' -Recurse

Finds the orphaned files in "E:\Dir1" and any of its subdirectories in addition to the default directories.

Example: 5

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -LocalOnly

Returns only the local file paths for orphaned files.

Example: 6

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014 -RemoteOnly

Returns only the remote file path for orphaned files.

Example: 7

PS C:\> Find-DbaOrphanedFile -SqlInstance sql2014, sql2016 -FileType fsf, mld

Finds the orphaned ending with ".fsf" and ".mld" in addition to the default filetypes ".mdf", ".ldf", ".ndf" for both the servers sql2014 and sql2016.

Required Parameters

-SqlInstance

The target SQL Server instance or instances. You must have sysadmin access and server version must be SQL Server version 2000 or higher.

Optional Parameters

-SqlCredential

Login to the target instance using alternative credentials. Accepts PowerShell credentials (Get-Credential).
Windows Authentication, SQL Server Authentication, Active Directory - Password, and Active Directory - Integrated are all supported.
For MFA support, please use Connect-DbaInstance.

-Path

Specifies additional directories to search beyond the default SQL Server data and log paths. Use this when databases were stored in non-standard locations or when you suspect orphaned files exist in
custom backup/restore directories. Accepts multiple paths and searches them alongside the automatically detected SQL Server directories.

-FileType

Specifies additional file extensions to search for beyond the default database file types (mdf, ldf, ndf). Use this to find orphaned Full-Text catalog files (ftcat), backup files (bak, trn), or other
SQL Server-related files. Do not include the dot when specifying extensions (use "bak" not ".bak").

-LocalOnly

Returns only the local file paths without server or UNC information. Use this when you need simple file paths for scripting file removal operations or when working with a single server. Not
recommended for multi-server environments since it omits which server the file belongs to.

-RemoteOnly

Returns only the UNC network paths to orphaned files. Use this when you need to access files remotely for cleanup operations or when building scripts that run from a central management server.
Provides the \server\share\path format needed for remote file operations.

-EnableException

By default, when something goes wrong we try to catch it, interpret it and give you a friendly warning message.
This avoids overwhelming you with "sea of red" exceptions, but is inconvenient because it basically disables advanced scripting.
Using this switch turns this "nice by default" feature off and enables you to catch exceptions with your own try/catch.

-Recurse

Searches all subdirectories within the specified paths in addition to the root directories. Use this when database files may be organized in nested folder structures or when conducting comprehensive
cleanup of complex directory hierarchies. Without this switch, only the immediate directories are searched.