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.
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:
- URL of site collections
- Primary Owner
- Secondary Owner
- Content Database
- Storage Used
- Storage Warning
- Storage Max
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'
Thanks and what about given below properties? how to use?
Storage Used
Storage Warning
Storage Max
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());
can we generate same report using C#?
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);
}
}