SharePoint 2013 MMS Performance Issues

Loading the SharePoint term store page is for the most part a pain free process which takes no longer to load than many other SharePoint pages.

I have however recently been investigating poor performance when loading the term store management page (_layouts/15/termstoremanager.aspx).

When loading the page the message appears: ‘Term store operation in progress, this may take a few moments.’

To narrow down what was causing the delay in loading I enabled developer tools in my IE browser.

And I could see from the network capture that the GetGroups POST was taking up to 30 seconds in some cases.

As this issue was only being reported by term store administrators I also tested this with a general user with read only access to the term store and the performance was as expected with load times of a second or two.

So by simply adding myself to the term store administrators group I could recreate the issue.

Digging into the ULS logs I could see that SharePoint was in fact trying to resolve all of the users in the term store administrators group.

What I found was a large group of term store administrators in the farm (entire support and development team) and these were all listed in the term store administrators box shown below.

So before loading the page for term store administrators it will first be doing multiple name resolutions, also any users listed in the term store administrators or managers groups but no longer in AD will cause extra delay in this process.

SPRequest.GetNTFullNamefromLoginEx: UserPrincipalName=, AppPrincipalName= ,bstrLogin=domain\testuser

Microsoft.SharePoint.Taxonomy.WebServices.TermStoreGenericObject..ctor(Group group) at Microsoft.SharePoint.Taxonomy.WebServices.TermStoreGenericObject..ctor(Group group)     at Microsoft.SharePoint.Taxonomy.WebServices.TaxonomyInternalService.GetGroups

SPAce encoded user claim i:0#.w|domain\testuser cannot be resolved. Microsoft.SharePoint.SPException: Cannot complete this action.  Please try again. ---> System.Runtime.InteropServices.COMException: Cannot complete this action.  Please try again.     at Microsoft.SharePoint.Library.SPRequestInternalClass.GetNTFullNamefromLoginEx(String bstrLogin, Boolean& pbIsDL)

Part of the call stack is shown below:

Microsoft_SharePoint!Microsoft.SharePoint.Library.SPRequest.GetNTFullNamefromLoginEx(System.String, Boolean ByRef)

Microsoft_SharePoint!Microsoft.SharePoint.Utilities.SPUtility.GetFullNameFromLoginEx(System.String, Boolean ByRef)

Microsoft_SharePoint!Microsoft.SharePoint.Administration.SPAce`1[[Microsoft.SharePoint.Taxonomy.TaxonomyRights, Microsoft.SharePoint.Taxonomy]].get_DisplayName()

Microsoft_SharePoint_Taxonomy!Microsoft.SharePoint.Taxonomy.WebServices.TermStoreGenericObject..ctor(Microsoft.SharePoint.Taxonomy.Group)

Microsoft_SharePoint_Taxonomy!Microsoft.SharePoint.Taxonomy.WebServices.TaxonomyInternalService.GetGroups(System.Guid, System.Guid, System.Guid, Boolean, Int32)

To improve the performance for the term store administrators I recommend using an AD security group in the place of individuals names.

On page load only the AD group will be resolved in AD to ensure it exists, none of the group members will be resolved and should therefore reduce the number of name resolutions your page has to complete before loading its content.

I hope this post helps you understand more about this page and what happens on loading, as it took me long hours of investigation to get to the bottom of the performance issues I faced.

Thanks for reading,

Matt

Deleting Orphaned Sites From SharePoint Content Database

Recently I was carrying out a cumulative update release on a client SharePoint farm, when running the Test-SPContentDatabase commandlet I was faced with an orphaned site which could cause upgrade failures.

If you have orphaned sites in your content databases, get these cleaned up and tested in another environment before starting your production upgrade or it could be a very stressful time when sites within that content database will not load!

Test each content database before the upgrade:

Test-SPContentDatabase -Name ContentDB_Matt -WebApplication https://matt.test.com

In my case I was seeing the below errors for one database in particluar.

Category: SiteOrphan
Error: True
UpgradeBlocking: False
Message: Database [ContentDB_Matt] contains a site (Id = [********************], Url = [matt.test.com]) that is not found in the site map. Consider detach and reattach the database which contains the orphaned sites.
Restart upgrade if neccesary.


Category: SiteOrphan
Error: True
UpgradeBlocking: False
Message: Database [ContentDB_Matt] contains a site (Id = [*********************], Url = [matt.test.com]) whose url is already used by a diferent site, in database (Id = [***********************], name = [ContentDB_Matt], in the same web application. Consider deleting one of the sites which have conflicting urls.
Remedy: The orphaned sites could cause upgrade failures. Try detach and reattach the database whcih contains the orphaned sites.
Restart upgrade if neccesary.


You will need to use the id in the above error message, and do a Get-SPSite for the web application. If a site with the above ID is not returned via PowerShell then this orphaned site needs to be deleted.

Be carefull to check that the site ID in the error is not retuned in the Get-SPSite commands or you will be deleting a current site by mistake, take extra time to validate the site IDs before running any delete.

Using a non-production environment will give you a safety net here.

Using trusty old stsadm, enumerate all the sites in the content database to get a full list or sites and their subsites.

stsadm -o enumallwebs -database ContentDB_Matt

If you have orphaned sites you will see a mismatch in the number returned by enumallwebs and the number returned using Get-SPSite in PowerShell.

Get-SPSite -ContentDatabase "ContentDB_Matt" | select ID

Once you have compared the site IDs of those returned by enumallwebs and Get-SPSite you will have the IDs of the orphaned sites to be deleted.

Again stsadm comes to the rescue here, as within PowerShell I could not find the orphaned sites by ID.

stsadm -o deletesite -force -SiteID ********************* -DatabaseName ContentDB_Matt -DatabaseServer Matt_SQL_Instance

Run enumallwebs after the delete and that orphaned site should now be gone, and once all orphaned sites are deleted running enumallwebs and Get-SPSite should return a matching number of sites!

stsadm -o enumallwebs -database ContentDB_Matt

Get-SPSite -ContentDatabase "ContentDB_Matt" | select ID

Now when you run Test-SPContentDatabase it should not return errors about orphaned sites.

Good luck with your upgrades.

Matt

PowerShell to Output SharePoint Site Collection Database Information

Thanks to everyone who visits my blog, I have just reached 100,000 page views! I hope some of my posts have been helpfull to others in the SharePoint community.

I was asked by a member of the development team to extract some key details required for a large list of site collections in one of our farms.

I knocked up a quick script, and saved it to my library of handy reusable scripts.

I thought I would share this one, as it shows some basic fundamentals of using PowerSheell to extract informtion from SharePoint.

• Using an input file to pick up a variable (In this case site URL)
• Using a foreach loop to iterate through the variables in the input file
• Writing out the varibales being picked up during the foreach loop (you could also use 'out-file' instead of 'write-host' if you would prefer to send the output to a document)

Copy of the script below:

#Input file for your list of site collection URLs
$InputFile = get-content "D:\APPS\Scripts\input.txt"
foreach ($URL in $InputFile)
{
#Set your variables
$Site = Get-SPSite -Identity "$URL"
$SiteOwner = $Site.Owner
$SiteID = $Site.ID
$ContentDBID = $Site.contentdatabase.ID
$ContentDBName = $Site.contentDatabase
$ContentDBSize = $site.ContentDatabase.DiskSizeRequired
#Write the output to the PowerShell screen
write-host "Output for site:" -ForegroundColor Cyan
write-host $URL -ForegroundColor Green
write-host "Site Collection Owner: " $SiteOwner -ForegroundColor Green
write-host "Site Collections ID: " $SiteID -ForegroundColor Green
write-host "Content DB ID: " $ContentDBID -ForegroundColor Green
write-host $ContentDBName -ForegroundColor Green
write-host "Content DB Size (MB): "$ContentDBSize -ForegroundColor Green
}


The format of the input file is simply a list of URLs

Example:

https://testsite1.net
https://testsite2.net


Thanks for reading,
Matt

HRESULT: 0x80131904 SharePoint Database Full

As most companies who have adopted SharePoint will know, a huge part of any SharePoint project is migration of content from other legacy systems and file shares.

Even with the most controlled migration plan, there is always the possibiltiy of a proactive user manually dumping huge amounts of content on their SharePoint site (especially once they discover windows explorer view!).

When this occurs you will see sudden SQL growth and especially within the DocStreams table within the content database.

When a file is uploaded to SharePoint 2013 shredded storage breaks an individual BLOB into shredded BLOBs to be stored in the DocStreams table.

For some more detailed information regarding shredded storage check out this technet blog.

Back to the point, which may or may not be related to a migration. If a large amount of content is uploaded to your content database this can result in SQL storage issues.

You may see errors on the SharePoint pages, including HRESULT: 0x80131904

In the ULS log I could see that various tables were growing rapidly and filling their primary file group, namely the EventCache and AuditData tables due to the high usage.

ULS log errors:

Database full error on SQL Server instance 'SQL-SharePoint-Prd' in database 'ContentDB_Intranet'.
Additional error information from SQL Server is included below.  Could not allocate space for object 'dbo.EventCache'.'
EventCache_Id' in database 'ContentDB_Intranet' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.


Database full error on SQL Server instance 'SQL-SharePoint-Prd' in database 'ContentDB_Intranet'.
Additional error information from SQL Server is included below.  Could not allocate space for object 'dbo.AuditData'.
'AuditData_OnSiteOccurred' in database 'ContentDB_Intranet' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files,
dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

You will also see event viewer errors:

Event viewer error:

Database full error on SQL Server instance 'SQL-SharePoint-Prd' in database 'ContentDB_Intranet'. Additional error information from SQL Server is included below.

Could not allocate space for object 'dbo.DocStreams'.'DocStreams_CI' in database 'ContentDB_Intranet' because the 'PRIMARY'

filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup,

adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

You could do maintenance to shrink these DBs, but the likely fix for most people will be to add the required disk space to allow the required growth of the databases.

You should consider your autogrowth settings, have a read of this for more details.

To avoid issues like this in the future monitoring of the free space on the SQL disks is essential for your production environment and there are various tools out there to help you with this.

Thanks for reading,
Matt

SharePoint and Kerberos Incorrect SPN Error, The HTTP request is unauthorized with client authentication scheme 'Negotiate'

This post is related to an issue I have faced a couple of times in recent months, and I thought I would share my experience for anyone troubleshooting a similar issue.

My SharePoint web applications are set to use Negotiate (Kerberos), so I am assuming you have been through the process of setting up Kerberos authentication.

A great guide linked here by the way.

We have strict change control for production farms so I have only faced this in two staging farms where less stringent change management has meant configuration changes have been made and 'broken' Kerberos authentication.

But this could easily happen in a less tightly controlled production environment, which unfortunately I have too much experience with in the past!

The symptoms could be in many forms, depending how you are interacting with SharePoint.

I was trying to activate some features on a site and was receiving an error in PowerShell:

The HTTP request is unauthorized with client authentication scheme 'Negotiate'. The authentication header received from the server was 'Negotiate'

I then tried to browse the site in internet explorer and was prompted for my credentials, which would not be accepted.

There was no failing back to NTLM so I was stuck at this point.

To enable the Kerberos events in event viewer you will need to check you have the below registry entry:

HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\Kerberos\Parameters

Registry Value: 

LogLevel

Value Type: 

REG_DWORD

Value Data: 

0x1

If the entry does not exist, create it.

Check the event viewer under system events you should begin to see Kerberos events when trying to authenticate to the problematic site.

Now I could see it was clearly a Kerberos authentication issue and checking the SPNs is the first logical step.

Check the application pool account running the web application, lets say as an example it is: domain\MattTestAccount

For Kerberos to work this account should have SPNs assigned to it.

in command prompt:

setspn -l domain\MattTestAccount

You should then see all SPNs for that service account listed.

If the SPN for your web application is not displayed you have found the issue, and this was the the problem in my case.

How could this have happened when it was previously working?

In my instance, at some point the application pool account running the IIS site had been changed, maybe to make the app pool accounts consistent, maybe to have individual accounts per web app, there are a multitude of reasons but the important thing is you have found the root cause of the issue.

Now you need to set assign the SPN to the correct service account and your Kerberos authentication issue will be resolved.

Guidance on setting SPNs and all other required configuration is in the guide linked at the start of this post.

Thanks for reading,

Matt

Surfacing SQL Cube Data Through Excel Pivot Table and SharePoint Excel Services

Having recently done some projects involving data connections to SQL Cube data, one of the many way we surfaced the data to end users was via an Excel pivot table and Excel Services on a SharePoint page.

I thought I would share the configuration steps, as this is a simple and effective way to enhance the SharePoint content and provide your SharePoint users with some useful BI data.

In this example we are going to use a set of stored credentials to access the SQL data source and need to set this up in the SharePoint Secure Store service application.

I will refer to the SQL cube just as a data source, because most of this post is applicable when accessing other data sources whatever they may be.

In Central Administration > Service applications

Select 'New' from the ribbon to create a new target application.

Name your target application ID, in this case we will be using a group mapping. See the explanation on the left of the fields for more detail.

We just need the username and password for the service account we will use to connect to the data source, so we can leave this section as default.

Set the administrator, and the members.

The members are users who will be allowed to use the stored credentials to connect to the data source.

Now the ID has been created we can set the credentials.

Insert the service account credentials, this is the account which now needs to be permissions on the data source.

Now we have created the secure store target application ID we can open Excel.

In the data tab, click on connections.

Add a new connection.

You will need to browse, and find your data source files.

On my machine I have a data sources folder in my documents which contains some template .odc files.

Use one of these files to fire the wizard.

In this instance I am using the connection shown above for an SSAS server, again this could be used for a different data source type.

Enter the SQL server and instance name for the cube and leave the log on credentials as is for now, we will change this in the next step.

You user doing this configuration will need access to the cube or this will fail when you hit next, as it needs to connect to the server.

It has found the server, and I can choose the database and cube that I want.

Name your file, this will be saved to your data sources folder, before finishing we can set the authentication settings.

Here we can set it to use the stored credentials that we set in SharePoint at the start.

Lets now insert a PivotTable to test the connection.

Choose connection.

Select the data connection we have created.

The Pivot table of our data should now be added.

This  sheet will now load the PivotTable in Excel services when added to a SharePoint page, bear in mind that users you want to be able to load the data source will need to be members of the Secure Store target application ID so you could use AD groups if this is a large number of users.

Thanks for reading,

Matt