Saturday, June 21, 2014

Issues related to SharePoint Configuration Database

We were facing all kinds of strange issues which we could not explain. We saw Time-Out errors pointing to SQL. We were no longer able to create for example new Search Service Applications (Timed-Out), but also deleting them after was not possible. Attempts were done via GUI, PowerShell and STSADM but none of them worked.

Commands used for example:

$spapp = Get-SPServiceApplication -Name "Search Service Application - TEST"
Remove-SPServiceApplication $spapp -RemoveData

Get-SPServiceApplication –Identity

stsadm -o deleteconfigurationobject -id "xxx Identity"

We changed time out values for:

Changed App Pool Time out-values
Changed Central Admin Site Time Out Value
Changed webconfig file - added execution time out value

stsadm -o setproperty -pn database-connection-timeout -pv 900

But none of the above solved the issue until we found out that the SP Config DB was extremely big. When looking at the tables we saw this one:


When looking at the properties of this specific table and checking the storage used, the amount of data was extreme and the number of row counts was more than 110 million!!!

What was wrong?

There is a weekly timer job (by default) scheduled once a week which cleans up the Timer Job History for 7 days. This timer job was actually failing, it runs for about 5 minutes and a bit and then failed. It actually means that the amount of data in your DB just keeps on growing and growing.

First i found some PS scripts which i ran manually like below:

$test = Get-SPTimerJob | Where-Object {$ -eq "job-delete-job-history"}

$test.daystokeephistory = 200

So you have to change the values for the number of days you want to keep the history. Remember that to be able to run the job successfully you will have to check the dates in the table

So within SQL: use the Select Top 1000 Rows on the table and check the first dates you will see. If that date is for example 250 days ago , then change the value in the above script to 250 and make sure the script runs successfull.

When running the scripts, the log file will quicly start growing…

Later we found a very nice PowerShell script, thanks to Andy Rollings:

By using the below script, you start with your first date and just keep on monitoring in CA whether the jobs run successfull


Write-Host "Clearing Down Timer Job History"
$daysToKeep = 730
$daysToPurgeInOneLoop = 5

while ($daysToKeep -gt 0)
  $history = get-sptimerjob | where-object {$ -eq “job-delete-job-history”}
  Write-Host " "
  Write-Host -NoNewLine "Setting Days to Keep:"
  Write-Host -ForegroundColor Green $daysToKeep
  $history.DaysToKeepHistory = $daysToKeep
  Write-Host -ForegroundColor Green "Starting Purge Job"
  $lastTimeJobRan = $history.LastRunTime
  Write-Host -NoNewLine -ForegroundColor Green "Waiting For Purge Job to Complete"
  $jobFinished = $false
  while ($jobFinished -eq $false)
   Start-Sleep -Seconds 2
   $runningJob = Get-SPTimerJob $history.Name
   Write-Host -NoNewLine -ForegroundColor Yellow "."
   if ($lastTimeJobRan -ne $runningJob.LastRunTime)
    $jobFinished = $true
  Write-Host " "
  Write-Host -ForegroundColor Green "Ending Purge Job"
  $daysToKeep = $daysToKeep - $daysToPurgeInOneLoop

Write-Host -ForegroundColor Green "Setting Final Job History Retention to 3 days, and schedule to run daily @ 5am"
$history.DaysToKeepHistory = 3
Set-SPTimerJob -Identity $history -Schedule "Daily at 05:00"
Write-Host -ForegroundColor Yellow "Please check row counts on dbo.TimerJobHistory Table in Config DB to ensure run complete"


After the jobs have run (for me it took around 160 times!), we saved 82 GB on data. After shrinking the SP Config DB (data and logs), all issues were solved. no time-outs anymore, service applications could be created and removed……

4 weeks of troubleshooting solved!


Monday, June 2, 2014

Unable to create or access workbook cache


All of a sudden people started getting these errors while opening Excel files in the browser:

at C:\Windows\TEMP\Excel Server\FileCache\fa812204-b6ff-40ec-9209-fe8add3de89e\Ranges. Excel Services Application is unable to function without a workbook cache.

Thanks to below links, issue could be resolved


Unable to create or access workbook cache at C:\WINDOWS\TEMP\Excel Server\FileCache\SharedServices1-1078443246\Ranges. Excel Services is unable to function without a workbook cache.

(you can see this error in Event viewer)



Unknowingly you may have deleted the above mentioned folders. So, Excel services is not able to save the temporary files under the "Ranges" folder.


Login inside the SharePoint server and create the "Ranges" folder as per the above mentioned folder structure. If you are not able to see the "Excel Server" folder inside "C:\Windows\Temp" folder, just create it manually.

Monday, March 3, 2014

PowerPivot: One or more minidump files were found in the Logs directory, indicating a program crash


When looking at the SharePoint 2013 Health Analyzer, this error came up. Looking at the description, you will get to see this:


So, where are these minidump files stored..

Check the directory C:\Program Files\Common Files\microsoft shared\Web Server Extensions\15\LOGS

and you will see something like this:


Delete these files and run the


the error will disappear


Thursday, February 13, 2014

Only one Term Store shows up after creating multiple Managed Metadata Service Applications

After creating multiple Managed Metadata service applications (3) and even recreating them, while opening them, i only got to see one Term Store (the first one created).

And then i saw this post.... (thanks!)

I was working on a farm which would host both an Extranet and Intranet application. Each would have different taxonomies and require separate Managed Metadata Services (MMS).
Since the second MMS was only going to be used by a specific Web application, I did not place it in the default Service Application Association group when I created it.
Much to my surprise, it did not appear in Central Administration after I created it. After locating and deleting it with PowerShell, I created it again the same way with the same results.  Then the research and learning began:
  • A MMS must be in the default group to be able to manage it from Central Administration.
  • It must also be in the default group if you are going to create another service application that connects to it (like Semaphore) or the creation process cannot connect to it.
  • The MMS to be used only by specific Web application(s) should not be set to be default in the default group.
  • For the unique Web applications, create a custom Service Application Association group containing the MMS and whatever other services the Web application will consume. Set the MMS as default in the custom group but DO NOT REMOVE IT FROM THE DEFAULT GROUP.
If a MMS is not in the default group, then you can only manage it with PowerShell or by manually entering the URL at the Web application associated with the MMS. Use PowerShell to retrieve the GUID. Example:
(How is typing the GUID working out for you?)
Also, if you remove a MMS from the default group, other services cannot connect to it even though they have been configured to do so when it was in the default group.
To summarize:
When creating a second Managed Metadata Service and Semaphore Service Application, both must remain in the default Service Application Association group but should only be set as default in the Service Application Association group where they will be used by Web applications. If a Managed Metadata Service is not in the default group, it will not be visible or selectable when creating the Semaphore Service Application or other service applications which need to connect to it. It cannot be managed from Central Administration. For the Web applications which will be using these services, create a custom Service Application Association group containing these and other services consumed by the Web application and set them as default in the custom group.