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:
Its tedious to compare SQL Server databases one by one, For each web application, for each service application, isn't it? Well, lets use PowerShell to find out active SharePoint databases.

Step 1: Export SharePoint Database Names to a Text file
Lets 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 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.

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

No comments:

Please Login and comment to get your questions answered!

Powered by Blogger.