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.
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
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.
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’
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
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