Get Site Collection Size using PowerShell

Add-PSSnapin Microsoft.SharePoint.Powershell -ErrorAction SilentlyContinue
$Sites = (Get-SPContentDatabase Content_DB).Sites
$AllSites = @();
foreach($site in $Sites)
    $Site = Get-SPSite $site.Url
    $Size = $
    $Size = $Size/1mb
    $ASite = New-Object PSObject
    Add-Member -input $ASite noteproperty 'Url' $Site.url
    Add-Member -input $ASite noteproperty 'Size' $Size
$AllSites | Sort-Object descending  | Out-GridView

Search Federation Options in SharePoint On-Premises – Part II

In the first post Search Federation Options in SharePoint On-Premises I explained and demonstrated the steps required to setup Federated Search Results from 2 different farms.  To show the results I used two seperate web parts.  One confired to show results from Local SharePoint Result Source and other for the Remote resuls.  In this post I will explain the steps required to merge the results and show them as Result Block. 

To achieve this we hav to rely on two things. 

A Query Rule

Allow us to setup rule to make a search query against a different result source or promote the results based on based on some condition and action.  We can create Query Rules at Central Administration, Site Collection and Site Level.   We can also control when to make this rule active. 

A Result Block

A block of search results that will be displayed based ont he query rule.  we can define how much results will be shown, provide “More” links etc.

For more information please read How To Customize SharePoint 2013 Search Results Using Query Rules and Result Sources

Let’s setup the Query Rule and Result block.


The image above is what my results look like before I started.  Two web parts on the search results page.  First I removed the web part from page.  I saw something interesting.  The refinement web part broke. 


When I edit the webpart I found Target not found error.  I may have set the default web part to use the remote result source and 2nd web part to local search results.  Once I set the Refinement Targett to Local Search Results, it did not broke.

Creating the Query Rule

Open Central Adminsitration

Manage Service Application

Search Service Application

Under Queries and Results Click on Query Rules


From  “For what context do you want to configure rule”  select the Local SharePoint Results. 

Note: I am selecting it because I want to add this result block in default search results.

Then Click New Rule


Type in the Rule name.  You can limit the query based on results Sources, Query Categories and User Seagments.  I am not going into that details.

Select Query Contdition if any based on your requirements.  In my case I remove the condition entirely.  image
In the Actions section. You can either
Add a Promoted Results
It looks like Search keywords and best bets in SharePoint 2010 Search.  You just enter the title and URL and Click Save.

Add result Block
First you enter the Block Title to make it more clear
If you want to configure Query variable and change and test queries you can launc the Query Builder. 
From Search this Source Select the Remote result Source created in last blog post.
Select the number of Items to show in search results.
Expand the Settings Tab
Choose “More” link goes to the following URL and enter the URL of your Search center /pages/results.apsx with ?k={subjectTerms}

In case needed you can control when this query rule will be available and providethe contact.  image
The result block will be visible now.
Clic Save.
The rule will be visible on the Manage Query Rule page. image
Now Let’s test the search results. 
The first part of search results are Federated. 
Click on Show More took me to the remote search center. image
I perfomed the exact above step on my other farm to setup two way fedearted results.  Now when I clicked on more to go to other farm the results from first showed up as Federated. image

The same procedure is used to get federated results from Office365.  The link to Technet is here

Adding Users to Web App as Full Control

One of the most common issues I face while working with different environments is full control to all web applications in a SharePoint Farms.  The best way to tackle this issue for a team would be to create a AD Group and add the group to web application level using the same script but it is an issue where this method is not used so I faces issues browsing different web applications in different farms.  You can use the script below to fix this issues.

function Add-FullControl($UserName, $WebApp) 
    Write-Host "Adding $UserName as Full Control." -ForegroundColor Green
    $User = New-SPClaimsPrincipal -IdentityType WindowsSamAccountName -Identity $UserName
    $Policy = $WebApp.Policies.Add($User.ToEncodedString(), $UserName)   
    Write-Host "Done for $UserName." -ForegroundColor Green

foreach($WebApp in Get-SPWebApplication)
    $UserAccount = "contoso\sphelpdesk"
    Add-FullControl -UserName $UserAccount -WebApp $WebApp
Write-Host "All Done..." -ForegroundColor Green

Not a huge thing to do from UI but if you have big number of farms with many web apps this script will be handy.  The same script can be used for Cache Super User and Super Reader Accounts but minor changes to GetSpecialRole([Microsoft.SharePoint.Administration.SPPolicyRoleType]:: :)

SharePoint Server MVP for Another Year

I have been honourd to be SharePoint Server MVP for another year.  This will be my 6th year.  Special Thanks to Microsoft and to friends and family. 

Everything is slowly transitioning to Office365 lately but I am still involved in large On-Premises projects at HP.  I will keep sharing as much information as I have.  Best of luck to each and every one of you.  Once again huge thanks to Microsoft for recognizing the community.

Clearning up Large Lists and Recycle Bin with PowerShell

If you want to dump large lists quickly you can use batch delete script mentioend below.  Deleing items one by one in loop will take a lot of time.  Even if you delete the items they will get stuck in site collection recyle bin.  You can modify the script as needed.

Add-PSSnapin Microsoft.SharePoint.powerShell -ErrorAction SilentlyContinue 
$sitecollectionUrl =
$siteCollection = New-Object Microsoft.SharePoint.SPSite($sitecollectionUrl)
$web = $siteCollection.OpenWeb('Web') 

$list = $web.Lists["Logging List"] 

write-host("Logging List has : $list.ItemCount Items.") -ForegroundColor Yellow
write-host("Recyle Bin has : $siteCollection.RecycleBin.Count Items.") -ForegroundColor Yellow 

$itemCount = 0;
$listId = $list.ID;
[System.Text.StringBuilder]$batchXml = New-Object "System.Text.StringBuilder";
$batchXml.Append("<?xml version=`"1.0`" encoding=`"UTF-8`"?><Batch>");
$command = [System.String]::Format( "<Method><SetList>{0}</SetList><SetVar Name=`"ID`">{1}</SetVar><SetVar Name=`"Cmd`">Delete</SetVar></Method>", $listId, "{0}" ); 

$listItems = $list.Items
foreach ($item in $listItems)
    if($item -ne $null){$batchXml.Append([System.String]::Format($command, $item.ID.ToString())) | Out-Null;$itemCount++;}


$web.ProcessBatchData($batchXml.ToString()) | Out-Null;Write-Host("Starting to Clear Site Collection Recyle Bin: $siteCollection.RecycleBin.Count") -ForegroundColor Yellow 
Write-Host("Cleared Log List and Site Collection Recyle Bin.")

Changing Site Collection Quota for Personal Sites

During migration of personal Sites from SharePoint 2010 to SharePoint 2013 I saw an issue where most of Personal Sites quota is not assigned properly.  The reason could be the my site databases are not upgraded successfully.  I tried some basic PowerShell script to change the personal site quota but got the following error.


I even tried with Server OM with PowerShell but result was same.  So for now I will say just fix the database upgrade issue.

Get List of Personal Sites and Quota

Start-SPAssignment -Global
$PersonalSites = @();
$MySites = get-spwebapplication
foreach($Site in $MySites.Sites)
    if($Site.Url.Contains("personal")) #Ignore MySite Host
        $Site = New-Object PSObject
        Add-Member -input $Site noteproperty 'Url' $Site.Url
        Add-Member -input $Site noteproperty 'Quota' $Site.Quota
        $PersonalSites += $Site
Stop-SPAssignment -Global
$PersonalSites | Out-File C:\MySiteQuota.txt 

Change the Quota for Personal Sites

Start-SPAssignment -Global
$TemplateName = "Personal Site"
$contentService = [Microsoft.SharePoint.Administration.SPWebService]::ContentService
$quotaTemplate = $contentService.QuotaTemplates[$TemplateName] 

$MySites = Get-SPWebApplication | Get-SPSite -Limit ALL | 
  Where-Object {$_.ServerRelativeUrl.Contains("/personal/")} |
  ForEach-Object { $_.Quota = $quotaTemplate }
Stop-SPAssignment -Global

Controlling Maximum Site Count for Content Databases

Here is a quick and directy Powershell script to get and change the Maximum Site Count for the Content databases in SharePoint.  We had a large farm with hundred of databases so I used the following script to set the Maximum Site Count equal to current Sites in the database and Warning to less than 1.  You can change it based on the logic of your choice.

Get the List of Databases in a text file

$Databases = @();
foreach($aDB in Get-SPContentDatabase)
    $DB = New-Object PSObject
    Add-Member -input $DB noteproperty 'Name' $aDB.Name
    Add-Member -input $DB noteproperty 'Maximum' $aDB.MaximumSiteCount
    Add-Member -input $DB noteproperty 'Warning' $aDB.WarningSiteCount
    Add-Member -input $DB noteproperty 'Current' $aDB.Sites.Count
    $Databases += $DB
    Write-Host $aDB.Name + " " + $aDB.MaximumSiteCount + " " + $aDB.WarningSiteCount 
$Databases  | Out-File C:\DatabasesBefore.txt
#$Databases  | Out-GridView

Change the values

foreach($aDB in Get-SPContentDatabase)
        Write-Host $aDB.Name
        $MaxSiteCount = $aDB.MaximumSiteCount
        $WarningCount = $aDB.WarningSiteCount
        $CurrentCount = $aDB.Sites.Count 

        Write-Host "$aDB.Name $MaxSiteCount $WarningCount $CurrentCount" -ForegroundColor Yellow
            $aDB.MaximumSiteCount = $CurrentCount
            $aDB.WarningSiteCount = $CurrentCount - 1
            Write-Host "$aDB.Name $aDB.MaximumSiteCount $WarningCount $CurrentCount" -ForegroundColor Green 


Note: The script is excluding mysites databases and will fail for content DB which has no site collection so please add the logic based on your environment

You can re-run the first script if you want to verify the change but Central Administration –> Manage Content Database will show you the updates straight away.