Handling Orphaned SQL Database Files across the Estate with DBATools... the Pie-eyed Piper.

This may be an edge case issue, it may not. Or some may not know this is a potentially a thing. For any of the above questions, i'm not sure of the answer. I do know however, that it doesn't involve morally suspicious fairy tales of any kind, flutes, or pastry products for that matter.

I also know that it's something that could potentially be robbing disk space across SQL Estates so i'll talk about it anyway and supply a simple way to fix this in one sweep using the magnificent DBATools.

That thar Sneaky DROP DATABASE command.

Its a common (and wise) practice to to offline databases following a migration (to ensure nothing can update the old DB, and aid in a speedy rollback) , or as a temporary "safety catch" when decommissioning a database completely (to catch any sneaky missed connections). Thats all good and well, however if the DB is still in an offline state when it is deleted, the files are left on the server.

This is documented in the DROP DATABASE docs, but not stated particularly loudly. https://learn.microsoft.com/en-us/sql/t-sql/statements/drop-database-transact-sql?view=sql-server-ver16#general-remarks

So, it's possible that you may be left with orphaned database files across a server, and if the above is your standard practice, then across your estate.

You could do this by writing a script to check sys.master_files, and then reference the physical file names against the folder on your server to see what's left over. Then another script to connect and deletes these orphaned files. Nothing wrong with that at all, it would work absolutely peachy...but it all takes time.

In step DBATools.

Find-DbaOrphanedFile

Guess what this does? That's right, it does the checking for us of this exact issue.

Find-DbaOrphanedFile -sqlinstance server1

ComputerName : SERVER1

InstanceName : MSSQLSERVER

SqlInstance : SERVER1

Filename : D:\Data\MSSQLSERVER\OrphanedDB.mdf

RemoteFilename : \\SERVER1\D$\Data\MSSQLSERVER\OrphanedDB.mdf

ComputerName : SERVER1

InstanceName : MSSQLSERVER

SqlInstance : SERVER1

Filename : D:\Logs\MSSQLSERVER\OrphanedDB_log.ldf

RemoteFilename : \\SERVER1\D$\Logs\MSSQLSERVER\OrphanedDB_log.ldf

Note that it handily gives you the RemoteFileName too, which we will use below. Granted, its on an administrative path, but it allows an access point.

Great! It gives you the details of what mdf/ndf/ldf files are not associated with current databases on the instance in the current Data and Log paths for SQL. You can also specify additional file extensions and paths in the command, but i'm keeping this post simple.

But I have a Gazillion SQL instances!

No problemo, you can make use of your CMS for this. (You do have a CMS don't you?) https://learn.microsoft.com/en-us/sql/ssms/register-servers/create-a-central-management-server-and-server-group?view=sql-server-ver16

There's a DBATools function you can use to query this nicely.... Get-DbaRegServer.

Take a look here for a post on my favourite DBATools functions, of which there are many.

If you want to query a specific group, just specify the group name. This returns all of the servers within that group (and subgroups). Ie.. to return all servernames within the "Development Servers\SQL 2019" CMS group...

get-dbaregserver -sqlinstance CMSServer1 -Group 'Development Servers\SQL 2019'

Pipe Power!

As this is magnificent Powershell, we can pipe "|" the output of one command as the input of another.

That said, let us pipe the servers from our get-dbregserver command into Find-dbaorphanedFile.

get-dbaregserver -sqlinstance CMSServer1 -Group ''Development Servers\SQL 2019' | Find-DbaOrphanedFile | Select RemoteFileName

We now effectively run Find-DbaOrphanedFile across all servers found, and only show the RemoteFileName in a helpful UNC format. All in a single pass, no effort at all.

\\SERVER2\D$\Data\MSSQLSERVER\AnotherOrphanedDB.mdf

\\SERVER2\D$\Logs\MSSQLSERVER\AnotherOrphanedDB_log.ldf

\\SERVER1\D$\Data\MSSQLSERVER\OrphanedDB.mdf

\\SERVER1\D$\Logs\MSSQLSERVER\OrphanedDB_log.ldf

\\SERVER3\d$\Data\mssqlserver\YetanotherorphanedDB.mdf

\\SERVER3\d$\Logs\mssqlserver\YetanotherorphanedDB_log.ldf

Yo, badfiles... Let's go.

Time to Tidy things up. If you only want to report on what files are orphaned, then stop at this point!

Now, granted, for the below you may want a confirmation, or logging etc, as it will just remove your orphaned files in one hit. Again, we're just piping the output from Find-DbaOrphanedFile to a remove-item function.

get-dbaregserver -sqlinstance CMSServer1 -Group 'Development Servers\SQL 2019' | Find-DbaOrphanedFile | ForEach-Object {Remove-Item $_.RemoteFileName}

Orphaned Files be gone! Groovy. That was my boomstick.

That was just a single group in your CMS. You can use the above on as small, or as large a scale as you require.

Hope this helps to keep your estates in great shape, without breaking a sweat. As we don't like avoidable effort.

Thanks for reading.

Rowdy Roddy Piper

Original image courtesy of www.clker.com