Using dbatools to find orphaned files

A lot of times you will move a database file from one drive to another and then forget to delete the original. This takes up precious space and could be causing you low disk space. Using dbatools, you can easily find any orphaned files for 1 database or all of the servers in your estate.

To read my previous article on how to set up dbatools and the benefits of using it, follow this link: DBATOOLS.IO….A DBA timesaver/luxury

Assuming you read the article above or know how to use PowerShell, open PowerShell as an administrator.

At the prompt, use the following command substituting your server name for etlsqldb.

PS C:> Find-DbaOrphanedFile -SqlInstance etlsqldb

After pressing Enter, the program returns no results. That is good, this server does not have any orphaned files.

Now the magic happens. Since we are using PowerShell and dbatools, we can set up variables and have it check multiple servers at once.

Below, I am creating a variable called $ServerList which will hold all of my server names. Just put in your server names within double quotes like below.

PS C:> $ServerList = "AMRDBARC", "AMRDBPROD", "ETLSQLDB", "LANSWEEPER"

Now I can run the same command but for the -SqlInstance, I will pass in the $ServerList variable.

PS C:> Find-DbaOrphanedFile -SqlInstance $ServerList

Above it found 1 server that has orphaned files. Lets double check by checking in SSMS.

I look in the database properties and the path is pointing to the X: drive, so the files above we confirmed that they are not needed.

Now we can delete the 2 files (tempdb.mdf and templog.ldf) from the above list and reclaim a lot of disk space.

To find out the over 500 commands that dbatools can do, please follow this link and make your life easier: https://dbatools.io/