Wednesday, June 20, 2012

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
Site collection's content database in SharePoint 2010

PowerShell Script to get site collection's content database:
This code outputs the content database name of given site collection.
$site=Get-SPSite "
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 > 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 report with site collection databases information. PowerShell comes to 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 ''

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:


  1. can we generate same report using C#?

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

      SPWebApplication webApp = SPWebApplication.Lookup(new Uri(""));

      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);

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

    Storage Used
    Storage Warning
    Storage Max

    1. Here you go:
      //Get Storage Used
      //Get Maximum Storage & Warning Level - Applicable ONLY When Quotas are applied


Please Login and comment to get your questions answered!

You might also like:

Related Posts Plugin for WordPress, Blogger...