Orphan SharePoint sites vs orphan content databases

Note: the article below is for on-prem SharePoint. For Microsoft 365 orphan/ownerless resources – sites, groups – please check 
– Ownerless Microsoft 365 groups, teams and sites Q&As 
– Microsoft 365 ownerless groups policy email template format and content
– Orphan Microsoft 365 groups in large environments

Remove orphan sites from SharePoint content database

Problem: When you patch SharePoint or perform database-attach migration or just do Test-SPContentDatabase, sometimes you can see errors with category “SiteOrphan”. Although it says “UpgradeBlocking : False”, Ignoring this error may cause severe issues, even data loss.

Problem:
When you patch SharePoint or perform database-attach migration or just do Dismount-SPContentDatabase and Mount-SPContentDatabase or Test-SPContentDatabase, sometimes you can see errors with category “SiteOrphan“, like this:

Message         : Database [...] contains a site (Id = [...], Url = [...]) that is not found in the site map. Consider detach and reattach the database.
Remedy          : The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.
or
Message         : Database [] contains a site (Id = [...], Url = [...]) whose url is already used by a different site, in database (Id = [...], name = [...]), in the same web application.
                  Consider deleting one of the sites which have conflicting urls.
Remedy          : The orphaned sites could cause upgrade failures. Try detach and reattach the database which contains the orphaned sites. Restart upgrade if necessary.

Although it says “UpgradeBlocking : False“, Ignoring this error may cause severe issues, even data loss.
E.g. scenario: you are patching large farm and you
– detached content databases
– patched farm
– attached content databases
all went good, without any errors, but!
Actually you lost some site collection and even did not notice!
This happens if you attach database with orphan site before “good” database.

So it is very important to address the SiteOrphan issue before it’s too late.

How?
Remove-SPDeletedSite doesn’t work.
Remove-SPSite -Identity <orphan site Id> doesn’t work too.
There is no powershell command Remove-SPOrphanSite.

The solution

Trevor Seward :
“Typically when these types of errors occur, it is best to abandon the Content Database.
Create a new Content Database on the Web Application, then use Move-SPSite to migrate each Site Collection to the new database.
Once you’ve migrated to the new Content Database, perform an iisreset, then detach the old Content Database.”
https://sharepoint.stackexchange.com/questions/169695/remove-orphans-site-collections-inside-my-content-database-raised-the-following

Vladilen:

Move-SPSite is very gentle (fragile) and unstable command. It depends on environment, but in my experience you can be safe moving <3GB site collections.

The bigger the site – the more risk it will fail. The bad news is when move-spsite fails, you have another orphan site in target content database, so it is a good idea to drop this database (not to use this failed target database anymore).

Before Move-SPSite for big sites, I’d suggest for “target” content database (maybe for source):
– out of availability group
– simple SQL recovery model