Find in Which Content Database Site Collection Lives

Requirement: Get which site collections resides in which content database.

Solution: Simplest method is: Navigate to Central Admin >> Application Management >> View All Site Collections.  This will display the content database name of a particular site collection.

site collection's content database sharepoint 2010

PowerShell Script to get site collection’s content database:

This code outputs the content database name of the given site collection.

$site=Get-SPSite "https://sharepoint.crescent.com
write-host $site.ContentDatabase.Name

One-Liner to Find Content Databases of Site collections in SharePoint 2013:

Get-SPWebApplication | Get-SPSite | Select URL, @{Name="Database"; Expression={$_.ContentDatabase.Name}} 

Using STSADM to get Content Database of All site collections: Execute the STSADM as:

STSADM -o enumSites -url https://SharePoint.Company.com > SitesList.xml

This will generate the SitesList.xml file with following information:

  1.  URL of site collections
  2.  Primary Owner
  3.  Secondary Owner
  4.  Content Database
  5.  Storage Used
  6.  Storage Warning
  7.  Storage Max
get site collection database

Report for site collection content database in SharePoint:

On a regular basis, Had to generate reports with site collection database information. PowerShell comes to the rescue again!

function SitesInDB([string]$url)
{

[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint") > $null
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SharePoint.Administration") > $null

$rootSite = new-object Microsoft.SharePoint.SPSite($url)
$webApplication = $rootSite.WebApplication

$ContentDBCollection = $WebApplication.ContentDatabases

$DBName = [Microsoft.SharePoint.Administration.SPContentDatabase].GetProperty("Name")


#If Data directory already exists remove it
if (Test-Path "D:\Reports\SitesInDB\Data")
{
    Remove-Item "D:\Reports\SitesInDB\Data" -Recurse
}

# create a folder
mkdir D:\Reports\SitesInDB\Data

# Traverse through all the content databases and run the stsadm command
foreach($ContentDB in $ContentDBCollection){  
 $CurrentDBName = $DBName.GetValue($ContentDB, $null)
 stsadm -o enumsites -url $url -databasename $CurrentDBName > D:\Reports\SitesInDB\Data\$CurrentDBName.txt
 }

# check the folder "Data-YEAR-MM-DD exists already if so delete it
$newName = get-date -uformat "%Y-%m-%d"
if (Test-Path "D:\Reports\SitesInDB\Data-$newName")
{
    Remove-Item "D:\Reports\SitesInDB\Data-$newName" -Recurse
}

#rename the folder "Data" to "Data-YEAR-MM-DD"
ren D:\Reports\SitesInDB\Data D:\Reports\SitesInDB\Data-$newName
}

#Call the function
sitesInDB 'https://SharePoint.crescent.com'

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!

4 thoughts on “Find in Which Content Database Site Collection Lives

  • Thanks and what about given below properties? how to use?

    Storage Used
    Storage Warning
    Storage Max

    Reply
    • Here you go:
      //Get Storage Used
      Console.WriteLine(oSPSite.Usage.Storage.ToString());
      //Get Maximum Storage & Warning Level – Applicable ONLY When Quotas are applied
      Console.WriteLine(oSPSite.Quota.StorageMaximumLevel.ToString());
      Console.WriteLine(oSPSite.Quota.StorageWarningLevel.ToString());

      Reply
  • can we generate same report using C#?

    Reply
    • Sure Tariq, Why not! Here is the code to start with.

      SPWebApplication webApp = SPWebApplication.Lookup(new Uri(“https://sharepoint.crescent.com”));

      foreach (SPContentDatabase cdb in webApp.ContentDatabases)
      {
      foreach (SPSite oSPSite in cdb.Sites)
      {
      Console.WriteLine(“Site:” +oSPSite.RootWeb.Title +” at “+oSPSite.RootWeb.Url + ” lives in the database:” + cdb.Name);
      }
      }

      Reply

Leave a Reply

Your email address will not be published.