Find and Delete Unused Orphan Databases in SharePoint

Problem:
In a SharePoint 2013 pilot environment, created several Web applications and service applications for testing purposes. Some of those web applications are deleted without deleting their databases. After a while, The database server was filled up, and we got to clean up the SQL Server by deleting unused SharePoint databases.

Solution:

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
Your task is much easier if you have any text comparison utilities like BeyondCompare. 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 
Compare-Object -ReferenceObject $SharePointDBs -DifferenceObject $SQLDBs

Now, You got all the unused databases. Open SQL Server Management Studio, identify each unused database from the above comparison, and 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 references 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}
$OrphanDBs

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

The database is deleted but still referenced in the Web Application?

(Get-SPWebApplication https://sharepoint.crescent.com).ContentDatabases

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 https://sharepoint.crescent.com
$webApp.ContentDatabases.Delete("Database-GUID")

Salaudeen Rajack

Salaudeen Rajack - SharePoint Expert with Two decades of SharePoint Experience. Love to Share my knowledge and experience with the SharePoint community, through real-time articles!

Leave a Reply

Your email address will not be published. Required fields are marked *