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/

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s