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 "http://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 http://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 'http://SharePoint.crescent.com'

Salaudeen Rajack

Information Technology Professional with Two decades of SharePoint Experience.

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

  • March 5, 2013 at 6:04 AM

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

    Storage Used
    Storage Warning
    Storage Max

    Reply
    • March 5, 2013 at 7:47 AM

      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
  • March 4, 2013 at 1:31 PM

    can we generate same report using C#?

    Reply
    • March 4, 2013 at 2:26 PM

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

      SPWebApplication webApp = SPWebApplication.Lookup(new Uri(“http://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