none
site collection size list in excel resided in particular content dabase RRS feed

  • Question

  • I want a site collection size in excel sheet for the particular content database. Please let me know the exact command to get the list of sites with sizes for a particular content database. 

    note: it should be from particular database

    THank you in advance


    • Edited by SravanC Monday, July 22, 2019 11:06 AM
    Monday, July 22, 2019 9:30 AM

Answers

  • I am not sure what exactly your are getting that error. try with the below code.

    Add-PSSnapin microsoft.sharepoint.powershell
    $CustomResult=@() 
    $webAppUrl = "https://Your_Web_App_Url"
    $SiteCollections =  Get-SPSite -WebApplication $webAppUrl -Limit All # get all site collections
    $SiteCollections | ForEach-Object { 
      
         $CustomResult += [PSCustomObject] @{ 
         SiteColUrl = $_.Url 
         Database = $_.ContentDatabase.Name
         SizeinMB = [System.Math]::Round((($_.Usage.Storage)/1MB),2) 
         SizeInGB = [System.Math]::Round((($_.Usage.Storage)/1GB),2)
         }           
    }
    $CustomResult | Select SiteColUrl,Database,SizeinMB,SizeInGB, fl | Out-File D:\Size.txt
    Thanks & Regards,


    sharath aluri


    Tuesday, July 23, 2019 10:46 AM

All replies

  • Hi Sravan,

    The below script will Get all content databases and their Site collections along with size from that you can take required content database as well:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
     
    #Get all SharePoint content databases available
    $ContentDatabases = Get-SPContentDatabase
    #Loop through each content database
     foreach($ContentDb in $ContentDatabases)
      {
       Write-Host "`nContent Database Name:  $($ContentDb.Name) Size:$($ContentDb.DiskSizeRequired/1MB) MB " -ForegroundColor DarkGreen
     #Get all site collections in the content database
     Write-Host "Site Collections List:" -ForegroundColor Blue
         foreach($site in $ContentDb.Sites)
         {
            write-host $site.url
         }
     }

    The above Script will get you Database Size along with the list of site collections in it. example like below:

    Content Database Name:WSS_Content_4 Size:2518 MB 
    Site Collections List:
    https://Site_Url/sites/1
    https://Site_url/sites/2
    https://Site_url/sites/3

    After you got the list of site collections for each database then simply run the below command. this will get you size of each site collection in a .txt file. don't forgot to provide your path at last.

    Get-SPSite -Limit All | select url, @{label="Size in MB";Expression={$_.usage.storage/1MB}} | Sort-Object -Descending -Property "Size in MB" | ConvertTo-Html -title "Site Collections sort by size" | Set-Content D:\SC.txt

    Thanks & Regards,


    Sharath Aluri


    Monday, July 22, 2019 11:24 AM
  • Hi Sharath,

    Thank you for the response.

    First script it is giving the output list of all databases along with site collection in the respective db but not sizes

    second script output is in html and it is not clear which is not giving site collection output randomly. can we get the report of site collection with its sizes accurately for the particular DB in the excel format?  



    • Edited by SravanC Tuesday, July 23, 2019 9:15 AM
    Tuesday, July 23, 2019 9:09 AM
  • I am not sure what exactly your are getting that error. try with the below code.

    Add-PSSnapin microsoft.sharepoint.powershell
    $CustomResult=@() 
    $webAppUrl = "https://Your_Web_App_Url"
    $SiteCollections =  Get-SPSite -WebApplication $webAppUrl -Limit All # get all site collections
    $SiteCollections | ForEach-Object { 
      
         $CustomResult += [PSCustomObject] @{ 
         SiteColUrl = $_.Url 
         Database = $_.ContentDatabase.Name
         SizeinMB = [System.Math]::Round((($_.Usage.Storage)/1MB),2) 
         SizeInGB = [System.Math]::Round((($_.Usage.Storage)/1GB),2)
         }           
    }
    $CustomResult | Select SiteColUrl,Database,SizeinMB,SizeInGB, fl | Out-File D:\Size.txt
    Thanks & Regards,


    sharath aluri


    Tuesday, July 23, 2019 10:46 AM
  • Hi Sharath,

    Below Command also worked like a charm corresponding to particular database with their site collection sizes. 

    Get-SPSite -limit all -contentdatabase DATABASENAME | select url, @{label="Size in GB";Expression={$_.usage.storage/1GB}} | Sort-Object -Descending -Property "Size in GB" | export-csv D:\XXX.csv



    • Edited by SravanC Wednesday, July 24, 2019 9:37 AM
    Wednesday, July 24, 2019 9:36 AM