A friend was doing an analysis on a client system with Always On Availability Groups where he was preparing some database changes. He found some strange differences between the mount points on both servers when getting the replicas' disk space information with the following command:

Get-DbaDiskSpace -ComputerName REPLICA1, REPLICA2 | Where-Object Label -like '*Data*' | Sort-Object Label

As we can see on the picture, the Data01 and Data02 mount points on both servers had different amounts of free space.

He asked me if dbatools has any command that could help demystify these differences.

TL;DR

No. But dbatools has lots of goodies (commands) and whenever you can't get what you want with just one command, build a script! Let's write some commands and then we can save it on a script that we can save and reuse later in similar situations.

I will explain a few different checks that we can use to try to demystify this.

Look for Orphan Files

After my friend asked the question, what came to my mind right away was orphan files. Orphaned database files are files not associated with any attached database on the SQL Server instance. Usually this happens whenever, as part of moving databases between mount points to re-arrange the free space, we copy the file to another mount point but then we forget to delete it on the source. Another option can be when we detach a database and leave the file there.

Using dbatools we can run the `Find-DbaOrphanfile` command that will help us to discover if my hunch was correct.

Find-DbaOrphanFile -SqlInstance REPLICA1

The command found one orphaned data file ('OrphanDatabase.mdf')

After a double-check, this 'mdf' file exists on both replicas, but the database was not mounted on any of the instances. He clarified this in a conversation with the client that confirmed to him that this database was detached some months ago so the files could be safely deleted.

Summary: we have just cleaned up 15GB.

Check for lost backup files

On a second hunch, I decided to check if there were any .bak files "lost" on the mount point. There aren't any dbatools commands for this, but fear not because we can use the Get-ChildItem cmdlet to search and list existing files on directories.

Get-ChildItem -Path \\REPLICA1\D$\Data01 -Filter *.bak -Recurse

This command will find all files that have a .bak extension in all existing folders, including subfolders. This last part is achieved by using the -Recurse parameter.

And...we found 1 file. But is this recent? It was some months old, but let's check the contents of the backup.

Note: I'm not sure about your experience but sometimes we need/want to do an isolated backup (with COPY_ONLY as an example) and because it's quicker or it is to delete after we copy it, we keep this on the local storage.

Reading the bak File

If you are thinking "well, maybe we have a dbatools command to read the backup header", you are right!

Read-DbaBackupHeader -SqlInstance REPLICA1 -Path D:\Data01\Backup.bak | Select-Object ServerName, DatabaseName, BackupFinishDate, RecoveryModel, IsCopyOnly, BackupTypeDescription, BackupSize, CompressedBackupSize, CompatibilityLevel

Here are some properties that might help you decide what this file contains:

• backup date (is it old enough?)
• a COPY_ONLY backup
• a backup that comes from a different server
• any other rule that you have to decide if you can delete or just move the file to a different location

After confirming this was an old backup that was no longer needed, he has deleted it.

Summary: we freed another 19.5GB, for 34.5GB.

Check for "stand-alone" Databases

Sometimes, clients don't have all databases within an availability group. I'm not talking about system databases, but other user databases. Maybe we have a "DBAAdmin" database on each replica.  Clients may also want to have different configurations, such as a database created for a temporary test or a newly created database that hasn't yet been added to an availability group.

With the following command, we will be able to find all databases that aren't part of an availability group.

Get-DbaDatabase -SqlInstance REPLICA1 -ExcludeSystem | Where-Object AvailabilityGroupName -eq '' | Select-Object Name, Size

Note: There are different ways to get this info. This is just an example.

In our case, we didn't find any databases outside of the availability groups, but maybe in your case will be different.

Compare Files on Mount Points

As a final check, let's see if we find different files between both replicas. In this case, I will just check one of the mount points where the data files are placed. This means comparing "D:\DATA01" of each REPLICA.

$replica1Files = Get-ChildItem -Path \\REPLICA1\d$\Data01 -Recurse
$replica2Files = Get-ChildItem -Path \\REPLICA2\d$\Data01 -Recurse
Compare-Object -ReferenceObject $replica1Files -DifferenceObject $replica2Files

Look what we have found: 9 database files that exist on just one replica, the REPLICA1. Well, at least on the exact path 'Data01'.

Using the Compare-Object with '-IncludeEqual'

We haven't found these databases as orphaned or "stand-alone". However, because we have two mount points, we decided to check if these files are on the D:\Data02. Let's compare the last result (contained on $differentFileList variable) with the content of the Data02 on the REPLICA2.

This time, we want to check if they exist. To do so we use the '-IncludeEqual' switch parameter and to ignore the different ones we also use the '-ExcludeDifferent'.

#Saving results to a variable
$differentFileList = Compare-Object -ReferenceObject $replica1Files -DifferenceObject $replica2Files  

#Get the files on the second mount point
$replica2FilesOtherMP = Get-ChildItem -Path "\\REPLICA2\d$\Data02\" -Recurse  

#Do the comparison but exclude the different ones and include the ones that match
Compare-Object -ReferenceObject $differentFileList.InputObject -DifferenceObject $replica2FilesOtherMP -IncludeEqual -ExcludeDifferent

As we can see, all files that didn't exist on the D:\Data01 on the REPLICA2, they exist on the D:\Data02 mount point.

This means that all 'mdf' data files for databases "Inventory_0091" until "0099" exist on D:\Data01 on REPLICA1 but, on REPLICA2 they are on the D:\Data02.

My friend had to move the files in order to make the file structure equal on both REPLICAS.

Wrap up

I hope this gave you a good overview of ways to find what's happening to your space on your SQL Server Always On Availability Groups replicas. We have seen that even when we don't have a single dbatools' command to achieve a task, we can take advantage of multiple ones and create a script to re-use later. In this scenario, we have used commands to search orphaned files, read the header of a backup file and check databases in and outside of availability groups.

Between some trash and misconfiguration, we were able to understand the existing differences regarding free space on the mount points.

Thanks for reading