dbatools is a free PowerShell module with over 180 SQL Server administration, best practice and migration commands included.


Find-DbaOrphanedFile

What is an orphaned file?

An orphaned database file is a data or log file that’s no longer part of an active database. Orphaned files can occur for instance when an offline database is deleted.

How does Find-DbaOrphanedFile work?

This command searches all directories associated with SQL database files for database files that are not currently in use by the SQL Server instance.

By default, it looks for orphaned .mdf, .ldf and .ndf files in the root\data directory, the default data path, the default log path, the system paths and any directory in use by any attached directory.

You can specify additional filetypes using the -FileType parameter, and additional paths to search using the -Path parameter.

Screenshots

find-dbaorphanedfile1

Examples

To log into the SQL Server “sqlserver2014a” using Windows credentials to search for orphaned files. Returns server name, local filename, and unc path to file.

Find-DbaOrphanedFile -SqlServer sqlserver2014a

To log into the SQL Server “sqlserver2014a” using alternative credentials and search for orphaned files. Returns server name, local filename, and unc path to file.

Find-DbaOrphanedFile -SqlServer sqlserver2014a -SqlCredential $cred

To Find the orphaned files in “E:\Dir1” and “E:Dir2” in addition to the default directories.

Find-DbaOrphanedFile -SqlServer sql2014 -Path ‘E:\Dir1’, ‘E:\Dir2’

To return only the local filepath. Using LocalOnly with multiple servers is not recommended since it does not return the associated server name.

Find-DbaOrphanedFile -SqlServer sql2014 -LocalOnly

To return only the remote filepath. Using RemoteOnly with multiple servers is not recommended since it does not return the associated server name.

Find-DbaOrphanedFile -SqlServer sql2014 -RemoteOnly

To find the orphaned ending with “.fsf” and “.mld” in addition to the default filetypes “.mdf”, “.ldf”, “.ndf” for both the servers sql2014 and sql2016.

Find-DbaOrphanedFile -SqlServer sql2014, sql2016 -FileType fsf, mld

Scenarios

In most circumstances you’d like to move or remove the orphaned files to make sure your drives are not cluttered with unused database files.

Move files

In this scenario the remote path will be queried and the files will be moved to another location. Use the -LocalOnly or -RemoteOnly parameters to create pipeable output.

Find-DbaOrphanedFile -SqlServer . -LocalOnly | Move-Item -Destination ‘E:\orphaned files’

Remove Files

The following scenario is similar to moving files but shows what would happen if you deleted all the orphaned files.

Find-DbaOrphanedFile -SqlServer sql1 -RemoteOnly  | Remove-Item -WhatIf

Use Out-GridView

Imagine you want to have some interaction on which files to move/remove, the above examples will not help you with that. There is a way to display the results from the function into a grid called “Out-GridView”. and from that grid select specific items. The selection will be used in further processing like moving or removing files.

In this scenario the orphaned files found will be displayed in a grid. The selected files will be removed, all other files will not be touched. The option “RemoteOnly” will be used to only get the files names returned. You can also use the option “LocalOnly” to get the local paths.

Find-DbaOrphanedFile -SqlServer sqlserver2014 -RemoteOnly  | Out-GridView -PassThru | Remove-Item

Author

This command was created by Sander Stad. You can find Sander on his blog and LinkedIn.

Get-Help

From PowerShell, execute Get-Help Find-DbaOrphanedFile -Detailed for more information on this function.

Source Code

Want to see the source code? View Find-DbaOrphanedFile.ps1 on GitHub
 

Related commands