Thursday, May 15, 2014

Find and Delete Unused Orphan Databases in SharePoint

Problem: 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:
s#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()} 

You might also like:
SharePoint Usage Reports
Usage reports, collaboration and audit for SharePoint.
Document SharePoint Farm
Automatically generate SharePoint documentation.

Check out these SharePoint products:

No comments :

Post a Comment

Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...