SharePoint – ConfigDB – Growing

Alright, so one of my developers was complaining that his config database on his development environment was more than 40GB(If your config database is larger than 10GB, you should continue reading), or actually it was the sysadmin that complained, but my developer was targeted. He asked if I could have a look at it. Sure of course, properly transaction logs gone wild…. So logging on the server, finding the trans logs were all good. All right further digging came up with the TimerJobHistory table being filled with around 100 million records. Alright, now what?

Some posts have some good Powershell scripts, that will incrementally delete your timerjob history, maybe also change your retention time. Sure I started to run those, but that didn’t quite do the trick. So what was wrong then?

Alright here is the deal. The “Delete Job History” job, has a timeout of 5 minutes. That means it will delete records, but if the delete is not completed within 5 minutes, it throws a timeout. It actually fails the timer job. (Timeout) This timer job is set to run only once a week. So guess what happens when you create more timer jobs per week than the Delete Timer job can remove in 5 minutes? – Yup, the TimerJobHistory table will grow…

I found the easiest fix, just to change the schedule for the “Delete Job History”. For the 100 million records, I changed it to every 7’th minute. I know that it will not run longer than 5 minutes, so a 2 minutes slack should be sufficient. So what should the setting be? Adjust according to your environment.

SharePoint ConfifDB growing beyond the normal 4-8GB. The data file, not the log file.

Root cause
The Delete Job History cannot delete more rows than created timer jobs per week within the 5 minute timeout. This can be related to heavy deployment or insufficient resources.

Change the schedule of the Delete Job History to run more frequently.

Get-SPTimerJob job-Delete-Job-History | Set-SPTimerJob -Schedule “daily at 05:00:00”

Which interval is required depends on your environment or the amount of rows in backlog. So set schedule accordingly and following the types for Set-SPTimerJob.
The type must be a valid SharePoint Timer service (SPTimer) schedule in the form of any one of the following schedules:
– Every 5 minutes between 0 and 59
– Hourly between 0 and 59
– Daily at 15:00:00
– Weekly between Fri 22:00:00 and Sun 06:00:00
– Monthly at 15 15:00:00
– Yearly at Jan 1 15:00:00

IIS – Authentication Settings – Powershell

Doing my work I had to setup some Kerberos authentication for an IIS site. The customer and the developer wanted it done using Powershell. I’m not all too impressed by the IIS powershell, it reminds me of WMI and I did not like that either.
Set that aside, I actually had a hard time finding something simple to get it to work.
All the below lines needs is the sitename, found from the IIS manager.

$SiteName = ‘’ #Example: 'Default Web Site'
$PSPath = “IIS:\Sites\$SiteName”

#-- ASP.NET Impersontation enable
Set-WebConfigurationProperty –filter system.web/identity –Name impersonate –Value True –PSPath $PSPath

#-- Windows Authentication enable
Set-WebConfigurationProperty –filter /system.webServer/security/authentication/WindowsAuthentication –Name enabled –value True –PSPath $PSPath

Adding Users/Groups – SharePoint – Powershell

A few times I’ve had to add users to specific SharePoint groups using powershell. I made the below script, which splits up each of the processes in the user creation and permission handling into transparent chunks. That way it’s easier to take what you need 🙂
The below users are external identity provider users, based on UPN. There is a domain users group being added also. The rest of the code should be self explanatory.

#Defines the site to work with
$URL= 'https://intranet.contoso.com/HR' 

#Gets the required web and site objects to work with
$Site= Get-SPSite $URL

#Creating Users
$JD=get-spweb $url | New-SPUser -UserAlias 'i:0e.t|Azure ACS|John.Doe@live.com'
$ON=get-spweb $url | New-SPUser -UserAlias 'i:0e.t|Azure ACS|Ola.Nordmann@live.com'
$AA=get-spweb $url | New-SPUser -UserAlias 'i:0e.t|Azure ACS|Anders.Andersen@gmail.com'
$MS=get-spweb $url | New-SPUser -UserAlias 'i:0e.t|Azure ACS|Medel.Svensson@contoso.com'

#Creating Groups
$DUContoso=get-spweb $url | New-SPUser -UserAlias 'c:0-.t|Azure ACS|Contoso\Domain Users'

#Get site default groups (using just "$web.Sitegroups" will show all of them.)
$HROwn=$web.SiteGroups["HR Owners"]
$HRMem=$web.SiteGroups["HR Members"]
$HRVis=$web.SiteGroups["HR Visitors"]

#Adding Users to groups



SharePoint 2013 – Workflow Management – Starting a workflow using Powershell

Had a simple task of creating a Powershell script that would be able to start a SharePoint workflow. Shouldn’t be too much a problem, I mean just get the web, get the list, find the associated workflows and start the right one using Powershell. How hard can it be?
Failing really short on examples on the web I had help from great colleagues again, puzzled together the below. The below code will start a specific workflow on all items within a designated list. The below should give the foundation to work with other management type tasks for SharePoint Workflows; Start, Start, Cancel etc.
I might update with additional examples, if needed 🙂

The Code

$sourceWebURL = '<URL>'
$sourceListName = '<List Name>'
$TargetWorkflow = '<Workflow Name>'
$spSourceWeb = Get-SPWeb $sourceWebURL
$spSourceList = $spSourceWeb.Lists[$sourceListName]

#Getting a Workflow manager object to work with.
$wfm = New-object Microsoft.SharePoint.WorkflowServices.WorkflowServicesManager($spSourceweb)
#Getting the subscriptions
$sub = $wfm.GetWorkflowSubscriptionService()
#Getting the specific workflow within the list of subscriptions on the specific list. (SP2010 associated workflows basically)
$WF = $sub.EnumerateSubscriptionsByList($spSourcelist.ID) | Where-Object {$_.Name -eq "$TargetWorkflow"}
#Getting a Workflow instance in order to perform my commands.

Foreach($item in $spSourceList){
	#Creating the dictonary object I need to parse into StartWorkflow. This could be most other workflow commands.
	$object = New-Object 'system.collections.generic.dictionary[string,object]'
	$object.Add("WorkflowStart", "StartWorkflow");
	$wfis.StartWorkflowOnListItem($WF, $item.ID, $object)

Microsoft.SharePoint.Client.WorkflowServices namespace

Additional credit
Frej Laursen, Joachim Bach & Per Jakobsen.

Warmup Script – SharePoint

Got tired of implementing huge warmup scripts, so decided to put together the simplest form. This will hit all site objects within the sharepoint farm.

the $ie.Visible = $true, should only be used in the development phase. It can be set to $false or completely omitted. With false or omitted, it will only spawn a process and not display the GUI of internet explorer.

add-pssnapin microsoft.sharepoint.powershell
$sites=get-spsite -Limit All
foreach ($site in $sites)
	$ie = New-Object -ComObject "InternetExplorer.Application"
	$url = $site.url
	$ie.Visible = $false
	#wait for page to load
	while ($ie.ReadyState -ne 4)
		sleep -Milliseconds 100

Edit 27-July-2016: Changed from single site to all SP sites.

SharePoint Farm Version Matched with Online XML

This script does what Microsoft could have done from the beginning. Provide an online XML file, with the different versions of SharePoint, so it is easier to match the build version number with the release name.

It will look at the farm version and match this with a publicly shared XML containing all SharePoint 2007, 2010 and 2013 releases. This XML will be updated as new updates are released.

Similar scripts will be produced for Exchange and Lync servers.

Note: This script has not been tested on SharePoint 2007 and earlier yet.

#-- Checks google.com for connection, writes status and proceeds with script if google.com is up
if(Test-Connection -computer google.com -count 1 -quiet) 
    Write-Host -ForegroundColor Green “Connectivity confirmed, proceeding with script...`r`n”
    #-- Clears all variables used throughout this script
    $dottedVersion, $buildVersion, $tempFile, $xmldoc, $i = $null
    #-- Setting working directory as current directory
    [Environment]::CurrentDirectory = $PWD
    #-- Name of xml file to use
    $tempFile = "spversion.xml"

    #-- If temp file already exists, remove it
    if(Test-Path $tempFile){Remove-Item $tempFile}

    #-- Checking if SharePoint Portal Services (SPS) 2001 is installed, based on file presence and getting version number
    if(Test-Path "C:\Program Files\Common Files\Microsoft Shared\MSSearch\Bin\mssrch.dll"){$dottedVersion = (Get-Command "C:\Program Files\Common Files\Microsoft Shared\MSSearch\Bin\mssrch.dll").FileVersionInfo.ProductVersion}
    #-- Checking if SharePoint Portal Server (SPS) 2003 is installed, based on file presence and getting version number
    elseif(Test-Path "C:\Program Files\SharePoint Portal Server\Bin\mssrch.dll"){$dottedVersion = (Get-Command "C:\Program Files\SharePoint Portal Server\Bin\mssrch.dll").FileVersionInfo.ProductVersion}
    #-- Checking if Windows SharePoint Services (WSS) 2.0 is installed, based on add/remove programs entry and getting version number
    elseif(Get-ChildItem HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall | % {Get-ItemProperty $_.PsPath} | where {$_.Displayname -match "Windows SharePoint Services 2.0"}){$dottedVersion = (Get-ChildItem HKLM:\Software\Microsoft\Windows\CurrentVersion\Uninstall | % {Get-ItemProperty $_.PsPath} | where {$_.Displayname -match "Windows SharePoint Services"} | select DisplayVersion).DisplayVersion}
    #-- Checking if SharePoint 2007 is installed, based on registry entry and getting version number
    elseif(Test-Path "HKLM:software\microsoft\shared tools\web server extensions\12.0"){$dottedVersion = (Get-item "HKLM:software\microsoft\shared tools\web server extensions\12.0").getvalue("version")}
        #-- Adds the PowerShell SharePoint snapin if it is not already added
        if((Get-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue) -eq $null){Write-Host -ForegroundColor Yellow "Adding Microsoft.SharePoint.PowerShell snap-in... " -NoNewline;Add-PSSnapin Microsoft.SharePoint.PowerShell; Write-Host -ForegroundColor Yellow "Done!"; $i = $True}
        #-- Gets farm version number objects for SP2010 or SP2013
        $buildVersion = (Get-SPFarm).Buildversion
        #-- Builds version string from objects aquired in the preceding line, by concatenating them seperated by a period 
        $dottedVersion = $buildVersion -join "."

    #-- Removes leading zeros from $dottedversion (if any exists)
    $dottedVersion = $dottedVersion -replace '(?&lt;=^|\.)0+(?=[^\.])',''
    #-- Downloads the public XML file
    Write-Host -ForegroundColor Yellow &quot;Downloading xml file... &quot; -NoNewline
    (New-Object System.Net.WebClient).DownloadFile(&quot;https://docs.google.com/uc?export=download&amp;id=0B3OwYqqW3V0aRUhiUlRJSnJQMTA&quot;, $tempFile)  
    Write-Host -ForegroundColor Yellow &quot;Done!`r`n&quot;
    #-- Saves the content of the downloaded file as an xml array
    [xml]$xmldoc = Get-Content $tempFile

    #-- Finds the build version in the xml file and gives you the relevant entry
    if(($dottedVersion.Split(&quot;.&quot;)[0] -eq 10) -or ($dottedVersion.Split(&quot;.&quot;)[0] -eq 11)){$xmldoc.SPVersions.Legacy.Version | Where-Object {$_.Build -eq $dottedVersion}}
    elseif($dottedVersion.Split(&quot;.&quot;)[0] -eq 12){$xmldoc.SPVersions.SP2007.Version | Where-Object {$_.Build -eq $dottedVersion}}
    elseif($dottedVersion.Split(&quot;.&quot;)[0] -eq 14){$xmldoc.SPVersions.SP2010.Version | Where-Object {$_.Build -eq $dottedVersion}}
    elseif($dottedVersion.Split(&quot;.&quot;)[0] -eq 15){$xmldoc.SPVersions.SP2013.Version | Where-Object {$_.Build -eq $dottedVersion}}

    #-- Removes the local xml
    Remove-Item $tempFile

    #-- Removes the PS snapin if added earlier in the script
    if($i){Remove-PSSnapin Microsoft.SharePoint.PowerShell}

#-- Show message and stop script if google.com is down
else{Write-Host -ForegroundColor Red -NoNewline “Connection is down… Please try again when you have internet access."}

Get, Set Time with Powershell

Had some challenges with time on some virtual environments that got reverted to snapshot each day and redeployed. Domain Controller was not connected to the Internet and host time synchronization caused some issues here. Basically we created a script which polled a public NTP pool and changed DC time accordingly, using remote powershell from server connected to DC and the Internet.

#--Find the FQDN of the DC.
$DC=([system.net.dns]::GetHostByAddress(([System.Net.Dns]::GetHostAddresses($(gwmi WIN32_ComputerSystem).Domain)[0].IPAddressToString))).hostname
#-- Creates Session to the DC.
$Session= New-PSSession -computername $DC -authentication Kerberos

#-- Collects time from dk ntp pool.
$Time = w32tm /stripchart /dataonly /computer:dk.pool.ntp.org /samples:1
#-- Gets local time of server
$LocalTime = Get-Date

#-- Synchronizes Local time with NTP pool time, using difference in time.
$SetTime= ($LocalTime.AddSeconds($Time[3].split("")[1].trim("+s")))

#-- Creates scriptblock to be run on DC
$SB= {param($SetTime)Set-Date $SetTime}

#-- Sets time according to dk.ntp.pool.org on Domain Controller
Invoke-Command $Session -ArgumentList $SetTime -Scriptblock $SB

#-- Removes Session to DC
Remove-PSSession $Session