Find and Delete Unused Orphan Databases in SharePoint

In a SharePoint 2013 pilot environment, Created several Web applications and service application for testing purpose. Some of those web applications are deleted without deleting their databases. After a while, The database server is filled up and we got to cleanup SQL Server by deleting unused SharePoint databases.


Using SharePoint 2013 Central administration to find out the databases used by web applications.

Get Active SharePoint Content Databases:
Navigate to Central Admin > Application Management > Manage Content Databases page. You can select and see which content databases are used by web apps.

Get Active SharePoint Service Application Databases:
Similarly, Navigate to Central Administration > Manage Service Applications >> Select the service application > Properties. This would show you what databases are used by the particular service application.

But, You got to repeat these steps manually for all web applications and service applications to find which ones you aren’t using.

PowerShell Solution to find out Orphaned Databases:

It’s tedious to compare SQL Server databases one by one, For each web application, for each service application, isn’t it? Well, let’s use PowerShell to find out active SharePoint databases.

Step 1: Export SharePoint Database Names to a Text file
Let’s export SharePoint database names to a text file. Run:

Get-SPDatabase | Sort-Object Name | Select -ExpandProperty Name | Out-File "c:\SharepointDBs.txt"

delete orphaned database sharepoint 2013
This gets you all databases associated with your SharePoint farm!

Step 2: Export Database names from SQL Server
Login to SQL Server Management Studio, Run this Query:

SELECT [name] FROM master.dbo.sysdatabases WHERE dbid > 4 

Step 3: Compare Both files
If you have any text comparison utilities like BeyondCompare, your task is much easier. Otherwise, You can use PowerShell to compare these two files and get the difference.

#Get SharePoint Databases 
$SharePointDBs = Get-Content "C:\SharePointDBs.txt"
#Get SQL Server Databases 
$SQLDBs = Get-Content "C:\SQLDBs.txt"

Compare-Object -ReferenceObject $SharePointDBs -DifferenceObject $SQLDBs

Now, You got all the unused databases. Open SQL Server Management Studio and identify each unused database from the above comparison and then delete any unused SharePoint database from SQL Server.

Important: Take backup before deleting any databases.

The database is actually deleted but still referenced in SharePoint?
What if the database is deleted and SharePoint still referencing it? Well, use this PowerShell script to find all deleted databases referenced:

#Find all deleted databases which are still referenced in SharePoint Farm
$OrphanDBs = Get-SPDatabase | Where {$_.Exists -eq $false}

#Remove all Deleted database references in SharePoint
Get-SPDatabase | Where{$_.Exists -eq $false} | ForEach {$_.Delete()} 

Database is deleted but still referenced in Web Application?


This gets you all content databases associated and referenced with the web application. From the output, Pick the orphan database id and supply it to:

$WebApp = Get-SPWebApplication

Salaudeen Rajack

Salaudeen Rajack is a SharePoint Architect with Two decades of SharePoint Experience. He loves sharing his knowledge and experiences with the SharePoint community, through his real-world articles!

Leave a Reply