Unable to open BDC Service Application UI from Central Admin site

Here is the issue definition that If we go to Central Admin – Manage service Applications -> Businees Datas Conectivity Service Application we obtain an error:

“Something went wrong” and a Correlation ID
Error message seen:
Event ID 8085 Event Viewer The BDC Service application Business Data Connectivity Service is not accessible. The full exception text is: Access is denied.
At logs:
SPIisWebServiceAuthorizationManager: SPIisWebServiceApplication with name ‘Business Data Connectivity Service’ and type ‘Microsoft.SharePoint.BusinessData.SharedService.BdcServiceApplication’ received request with ServiceSecurityContext whose primary identity has no valid data to check against ACL.
An exception occurred while writing a service call usage entry. Exception details: System.ObjectDisposedException: Safe handle has been closed
at System.Runtime.InteropServices.SafeHandle.DangerousAddRef(Boolean& success)
at Microsoft.Win32.Win32Native.GetTokenInformation(SafeTokenHandle TokenHandle, UInt32 TokenInformationClass, SafeLocalAllocHandle TokenInformation, UInt32 TokenInformationLength, UInt32& ReturnLength)
at System.Security.Principal.WindowsIdentity.GetTokenInformation(SafeTokenHandle tokenHandle, TokenInformationClass tokenInformationClass)
at System.Security.Principal.WindowsIdentity.get_User()
at System.Security.Principal.WindowsIdentity.GetName()
at System.Security.Principal.WindowsIdentity.get_Name()
at Microsoft.SharePoint.Utilities.SPUtility.GetCurrentThreadUserLogin(Boolean fFallbackToEnv)
at Microsoft.SharePoint.Administration.SPUsageManager.LogUsage(SPUsageEntry usageEntry)

The BDC Service application Business Data Connectivity Service is not accessible. The full exception text is: Access is denied.

From Central Administration Site when we try to open BDC service we have making a WCF request to Business Connectivity Service

Name=Request (GET:http://contoso.com:3760/_admin/BDC/ViewBDCApplication.aspx?AppId=ec61c2eb-a874-4dfd-8245-0476da3d2731)
WcfSendRequest: RemoteAddress: ‘http://contoso.com:32843/b02ca86c7cb94143bb8277579dbc505c/BdcService.svc/http’ Channel: ‘Microsoft.SharePoint.BusinessData.SharedService.IBdcServiceApplication’ Action: ‘http://www.microsoft.com/Office/2009/BusinessDataCatalog/BusinessDataCatalogSharedService/MetadataObjectCreate’
WcfReceiveRequest: LocalAddress: ‘http://contoso.com:32843/b02ca86c7cb94143bb8277579dbc505c/bdcservice.svc/http’ Channel: ‘System.ServiceModel.Channels.ServiceChannel’ Action: ‘http://www.microsoft.com/Office/2009/BusinessDataCatalog/BusinessDataCatalogSharedService/MetadataObjectCreate’

We have facing an authentication problem on Claims authentication. Looks that “User is not authenticated”

So it bring us to “Security Token Service” Application before calling BDC request

Claims Authentication af3y2 VerboseEx STS Call Claims Windows: Adding claim with type ‘http://sharepoint.microsoft.com/claims/2009/08/isauthenticated’, value ‘False’, value type ‘http://www.w3.org/2001/XMLSchema#string’, issuer ‘SharePoint’ and original issuer ‘SecurityTokenService’.
Claims Authentication af3y1 VerboseEx We are copying claim with type ‘http://sharepoint.microsoft.com/claims/2009/08/isauthenticated’, value ‘False’, value type ‘http://www.w3.org/2001/XMLSchema#string’, issuer ‘SharePoint’ and original issuer ‘SecurityTokenService’.

For Resolution and TroubleShooting suggestions

-> Check BDC Service Application has only Anonymous Authentication has enabled and “windows authentication” has disabled.
-> Check The Security Token Service Authentications are “Anonymous” and “Windows Authentication” has enabled.
-> Check IIS > SharePoint Web Services > Only Windows Auth should be selected.
-> Check BDC Service Application Anonymous Authentication Identity has set for “IUSR”
-> Check Top Level IIS Anonymous Authentication Identity has set for “IUSR”

1. Open IIS manager
2. Highlighted server name
3. Select Authentication from center pane
4. Highlight “Anonymous Authentication” and be sure it is Enabled
5. Click on “Edit…”
6. Select the “Specific User” radio box and click “Set”
7. Enter IUSR in the “User name:” box on the Set Credentials window.
— Note you do not need to enter a password.
8. Click OK to apply, then OK to apply.

Advertisements

Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

The error when the issue happens: (Related with legacyCAS issue)
Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.IO.FileLoadException: Loading this assembly would produce a different grant set from other instances. (Exception from HRESULT: 0x80131401)

Recently we have facing many issues with above exception .Effected products are SharePoint 2013 and SharePoint 2016.Mostly the cases are open as CritSit because of SharePoint down.

• We typically see these errors with non-SharePoint code running, such as SCOM’s APM agent or 3rd party monitoring software, or very unlikely .Net patches.
• Specifically in our case, we found the environment running APM monitoring Agent: APMAGT
• The most likely that the CLR core was trying to optimize a reload of the instrumentation agent with an incompatible CAS grant set.  Refer to https://msdn.microsoft.com/en-us/library/vstudio/dd984947(v=vs.100).aspx for a reference of CAS changes in ASP.NET 4.

Profiler tools somehow intercepting/effecting SharePoint’s loading/fallback mechanizm.Which is causing failure when loading incorrect image with permissions  (even the assembly is the same)  into an application domain.

“Today only the top level assembly’s grant set is checked. If later CLR sees an incompatible security grant set for any assemblies in the binding closure, CLR will not be able to recover from the error and will fail to load the assembly in the new appdomain. Essentially the host is responsible for not violating this constraint.”
https://blogs.msdn.microsoft.com/junfeng/2004/08/05/domain-neutral-assemblies/

I have seen many misredirected articles even in technet blogs and incorrect resolutions are suggested for resolving this problem.

One of them changing legacy cas model settings.
Which is out of the box for SharePoint products the Legacy CAS model is a requirement.So we have this value in every SharePoint web sites.
trust level="Full" originUrl="" legacyCasModel="true"
(This is correct!!!)
People are resolving this problem by setting legacyCasModel=”false” which is NOT SUPPORTED .

You shouldn’t change it because of backward compatibility and dependancy reasons we need that. Disabling this property cause anomalies.

Please be aware meaning of NOT SUPPORTED ,does not mean your SharePoint not work. It may work but  we may not give support unless you rollback your changes or can not forseen any other anomalies .

Another incorrect resolution.
Create a new registry DWORD value called LoaderOptimization and give it the value 1 within the key
“HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework”
Perform IISRESET and check Web Apps behavior.

In key ‘HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\.NETFramework’, create a new ‘DWORD (32-bit) Value’ named “LoaderOptimization” with a value 1 (either in decimal or hexadecimal as they are the same).
In key ‘HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Microsoft\.NETFramework‘, create a new ‘DWORD (32-bit) Value’ named “LoaderOptimization” with a value 1 (either in decimal or hexadecimal as they are the same).

This configuration will disable the loader optimization of assemblies that provoke the aforementioned behaviour by setting assembly loading into SingleDomain mode.

And this is also NOT SUPPORTED for SharePoint by Microsoft.
There is two reason for this.
1) These settings are not tested by product group. (It is not design to work in SingleDomain mode)
2) Let’s have look what is LoaderOptimization enumeration
https://msdn.microsoft.com/en-us/library/system.loaderoptimization(v=vs.100).aspx
We have setting “LoaderOptimization” as 1 which means the “SingleDomain” mode.
“Indicates that the application will probably have a single domain, and loader must not share internal resources across application domains.”
SharePoint have many assemblies with many different locations and permission, also have a very complex loading models depend on logic and fallbacks to CAS to GAC or vise versa.
Forcing SharePoint to use singledomain mode prevents the power of shareing loaded images and assembilies with different inter processes operations which effects performance and loading times and
SharePoint uses different permissions sets for appdomain level , multi domain is a requirement to provide all functionalities for SharePoint by security concerns.

And another one , this registry change may also effect your other .net applications (any kind of) because it is .net level change.

So what we should do ?
– First you should find it out which Dll(s) causing this problem. And verify the issue resolved when you uninstall related product.
Like
“Microsoft.EnterpriseManagement.OperationsManager.Apm.Instrumentation, Version=7.0.5000.0”
“Microsoft.EnterpriseManagement.OperationsManager.Apm.InstrumentationUtils, Version=7.0.5000.0”
– This is mostly a Third Party Monitoring tool or even Microsoft SCOM.
These kind of tools or a component belong these tools have inject their assemblies in SharePoint worker process.
And SharePoint is very special,complex blackbox what ever you say that we can not support if you do this.
For SCOM 2012/2016 we know the reason that APM component causing this.
And we have following article that you can not deploy APM for SharePoint servers. It is NOT SUPPORTED .

https://technet.microsoft.com/en-us/library/jj614617.aspx?tduid=(1dfb939b69d4a5ed09b44f51992a8b97)(256380)(2459594)(TnL5HPStwNw-v0X_tBOK3jzpbtaadMW8RA)()
Client-side .NET Application Performance Monitoring (APM) is not supported for SharePoint. Enabling client-side .NET Application Performance Monitoring for SharePoint can result in unpredictable application behavior and failures.

Well , i am not a SCOM expert but following action help you deploy SCOM without APM.

NOTE ! Workaround – Installing the SCOM Agent with the NOAPM=1 switch prevents the copy of the .NET APM related DLL’s as a result w3wp.exe process cannot load the problematic components which are available under C:\Program Files\Microsoft Monitoring Agent\Agent\APMDOTNETAgent\*.

1) Uninstall the SCOM Agent manually on the SharePoint Servers.
2) Delete the “C:\Program Files\Microsoft Monitoring Agent” folder on your SharePoint Servers.
3) Copy the SCOM Agent folder from your SCOM Management Server to the SharePoint Servers. The Agent folder can be found under C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\AgentManagement\amd64 on your SCOM Management Servers.
4) Open an elevated cmd window and on your SharePoint Server and install the SCOM Agent manually with the NOAPM=1 switch like in the example below.
Example : msiexec /i momagent.msi NOAPM=1
5) Afterwards please kindly install your Update Rollups. The current installed Update Rollup on your SCOM Management Server is also inside the Agent folder available.
C:\Program Files\Microsoft System Center 2012 R2\Operations Manager\Server\AgentManagement\amd64

May be this article also help for your SCOM agent deployments without apm.
https://blogs.technet.microsoft.com/kevinholman/2017/08/05/reinstalling-your-scom-agents-with-the-noapm-switch/

Recently we face some other issues with SCOM , even you have not deploy APM, it is still injecting some code in SharePoint which breaking our workerprocess.
In this condition please open a ticket for MS SCOM support team.
Related DLLs was;
“C:\Program Files\Microsoft Monitoring Agent\Agent\APMDOTNETAgent\V7.1.10184.0\PerfMon64.dll”

Another APM issue: “System Center 2016 Operations Manager APM Agent causing heap corruption in SharePoint”
https://support.microsoft.com/en-us/help/3216459/system-center-2016-operations-manager-apm-agent-causing-heap-corruptio

I hope this helps for correct actions.

Can not connect to SharePoint Store via Proxy

You have a SharePoint 2013 farm and you have configured apps as defined following articles;

Configure an environment for apps for SharePoint (SharePoint 2013)
https://technet.microsoft.com/en-us/library/fp161236.aspx

Enable apps in AAM or host-header environments for SharePoint 2013
https://technet.microsoft.com/en-us/library/dn144963.aspx

http://www.nothingbutsharepoint.com/2013/02/13/configure-an-environment-for-apps-for-sharepoint-2013-aspx/

(In this article we have assume that you dont have any configuration problem)

After you have enabled SharePoint Store Access , you have facing when we go to Apps and click on Purchase app’s we see an error “Sorry we cannot seem to connect to the SharePoint Store. Try again in a bit later” .

but when you check from Browser there is no Access issue for the Office.microsoft.com

You have suspecting that it may be a Proxy issue ; Good news thats very likely you right.

In normal condition we should see a credential prompt because of the Proxy Auth request but it is not happening well because it is worker process making that request not the browser.

To able to handle this issue , you should add https://store.office.com and https://office.microsoft.com to IE trusted sites because of automatic logon option should provide concurrent user credentials when proxy ask.

Another problem;

Proxy authentication can not be done by IIS Worker Process (where sharepoint run) even we have configured <defaultproxy> settings correctly in related web application web.config. we have seen in network traces SharePoint Worker Process somehow could not passing the credentials (we are expecting Application Pool Identity should in use) to proxy server making like an anonymous request so the Proxy server so it has been rejecting authentication . In our last log analysis and session we have identified the root cause of the problem : One of The IIS Site web.config settings is causing the issue <appSettings>
<add key=”aspnet:AllowAnonymousImpersonation” value=”true” />
</appSettings>

https://msdn.microsoft.com/en-us/library/hh975440(v=vs.120).aspx

To enable this setting, you must have IIS 7 or IIS 7.5 or upper running in Integrated mode (which SharePoint works like this)  When this setting is enabled, the application runs under the security context of the IUSR identity. Additionally, creating a Forms-based Authentication Web Application (Which Sharepoint in Claims mode we have to enable forms auth)  will enable the setting and set it to true.

This is a design issue of how IIS works. The issue happenning because of the application runs under the security context of the IUSR identity and the <defaultproxy>  useDefaultCredentials ”true” it passes IUSR’s credentials to proxy which is anonymous , it is not a real domain user.

For resolution:

– We can remove this from WFE servers but it is require to make some tests that you don’t facing the problem of “SharePoint impersonates the IUSR account and is denied access to resources” and verify your sites and sharepoint components are working correctly. To workaround the issues, you need to determine if the setting is mandatory for your environment, and if not, you can set it to ‘false’. https://support.microsoft.com/en-us/kb/2686411

Important
 : if you disable this feature, then anonymous users are impersonating the application pool account. Which would be an elevation of priviledge.

– You can add an exclusion on Proxy server from SharePoint Server internet requests may not required any authentication .

– You can test to create a simple proxy module (which requires custom code development and IIS module integration) handle the proxy authentication.

 

 

 

Delete inactive users in user profiles

For more detail there is a very good article about how mysite clean up job is working.
https://blogs.msdn.microsoft.com/kaevans/2012/06/25/inside-the-sharepoint-2010-my-site-cleanup-timer-job/

Well , if you have a scenario that you can not run somehow my site clean up job, or intentionally stopped for a reason and if you need to clean inactive user profiles following powershell script will help you to remove inactive (non-imported) profiles in User Profile Service in SharePoint.

#PowerShell Script – Delete Inactive User Profiles – SharePoint 2010/2013

#The scripts is distributet “as-is.” Use it on your own risk. The author give no warranties, guarantees or conditions.

if ((Get-PSSnapin “Microsoft.SharePoint.PowerShell” -ErrorAction SilentlyContinue) -eq $null) {
    Add-PSSnapin “Microsoft.SharePoint.PowerShell”
}

$site = Get-SPSite “<site url>
$ctx = Get-SPServiceContext $site
$pm = New-Object Microsoft.Office.Server.UserProfiles.UserProfileManager($ctx)

$ProfileDB = Get-SPDatabase | ? { $_.Type -eq “Microsoft.Office.Server.Administration.ProfileDatabase”}

$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = $ProfileDB.DatabaseConnectionString
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = “select NTName,RecordId from UserProfile_Full where bDeleted=1″
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()

Write-host “Total Count: ” $DataSet.Tables[0].Rows.Count
Write-Host “Following Inactive Accounts will be deleted !”

foreach($user in $DataSet.Tables[0].Rows)
{
   write-host “Planning to delete :” $user[“NTName”] -ForegroundColor Green
   $profile = $pm.GetProfile($user[“RecordId“])
    #To enable delete operation remove comment out for below line
    #$pm.RemoveProfile($profile)
    #write-host $user[“NTName”] is deleted!!! -ForegroundColor Red
}
write-host “Operation Completed !”

 

Workflow Manager 1.0 CU3

Hello Folks,

I have noticed recently we have released Cumulative Update 3.0 for Workflow Manager 1.0 at 30/12/2015
https://support.microsoft.com/en-us/kb/3104066

Important :
– For a prerequisite Service Bus 1.1 for Windows Server must be installed on the computer.
-You may have to restart the computer after you apply this cumulative update.

Some info on how to install the hotfix:
1. Install workflowmanagerclient_x64.msi first
2. Then install workflowmanager-kb3104066-x64.exe

After installing the fixes, open the workflow manager powershell and run these commands.
1. stop-sbfarm on any one server
2. start-sbfarm on any one server
3. stop-wfhost on each server
4. start-wfhost on each server

Issues that are fixed in this cumulative update

Issue 1:
The workflow manager back-end service crashes intermittently, and you may experience the following exception:

System.AggregateException: One or more errors occurred. —&gt; System.Net.ProtocolViolationException: Cannot send a content-body with this verb-type.
at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)
at Microsoft.Activities.Hosting.HostedHttpExtension.HttpRequestWorkItem.HttpRequestWorkItemAsyncResult.End(IAsyncResult result, Int32&amp; responseCode)
at Microsoft.Activities.Hosting.HostedHttpExtension.HttpRequestWorkItem.OnEndComplete(ScheduledWorkItemContext context, IAsyncResult result)
at Microsoft.Activities.Hosting.ScheduledWorkItem.EndComplete(ScheduledWorkItemContext context, IAsyncResult result)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.SingleNonTransactionalWorkItemComplete(IAsyncResult result)
—End of inner exception stack trace —
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.UpdateNonTransactionalWork()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.Isolate(AsyncCompletion callback, IAsyncResult result)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.SingleNonTransactionalWorkItemComplete(IAsyncResult result)
— End of inner exception stack trace —
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.UpdateNonTransactionalWork()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.Isolate(AsyncCompletion callback, IAsyncResult result)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.SingleNonTransactionalWorkItemComplete(IAsyncResult result)

Issue 2:
The workflow manager back-end service crashes intermittently with the following exception:

System.ArgumentException: An item with the same key has already been added.
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessWaiter()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessMatch(Boolean consumeInput)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.StartEpisode()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ReceiveNextMessage()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.UpdateNonTransactionalWork()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessNonTransactionalWork()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessWaiter()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessMatch(Boolean consumeInput)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.StartEpisode()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ReceiveNextMessage()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.UpdateNonTransactionalWork()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.Isolate(AsyncCompletion callback, IAsyncResult result)   at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ExceptionHandlingFrame(IAsyncResult result)

Issue 3:
The workflow manager back-end service crashes intermittently with the following exception:

System.ArgumentNullException: Value cannot be null.
Parameter name: key
at System.Collections.Generic.Dictionary`2.FindEntry(TKey key)
at System.Collections.Generic.Dictionary`2.TryGetValue(TKey key, TValue& value)
at Microsoft.Activities.Hosting.InputWaiterTable.InternalMatch(EventTraceActivity traceActivity, DateTime now, MessageDispatchProperties input, Guid currentInstanceId, Boolean removeWaiter, Guid currentSuspensionId, Boolean& consumeInput)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.StartEpisode()   at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ReceiveNextMessage()

Issue 4:
The workflow manager back-end service crashes intermittently with the following exception:

System.ArgumentException: An item with the same key has already been added.
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ProcessWaiter()
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.Isolate(AsyncCompletion callback, IAsyncResult result)
at Microsoft.Activities.Dispatcher.DispatchLoopInstanceAsyncResult.ExceptionHandlingFrame(IAsyncResult result)

Issue 5:
The restore scope operation fails, and you experience a unique key violation error as follows:

Restore-WFScope : Cannot insert duplicate key row in object ‘dbo.Activities’
with unique index ‘IX_Activities_Name_Version’. The duplicate key value is
(fef3537a-eefe-1340-5d86-335e62409f9c, IsEqualUser, 1).
The statement has been terminated.At C:\restoreScope.ps1:12 char:1

Issue 6:
Workflow manager front end can’t report notification errors if a workflow notification message contains properties differ only in case. Instead, it emits the following exception in the event log:

System.ArgumentException: An item with the same key has already been added.
at Microsoft.Workflow.Common.AsyncResult.End[TAsyncResult](IAsyncResult result)
at Microsoft.Workflow.Gateway.HttpAsyncResult.End[TAsyncResult](IAsyncResult result)
at Microsoft.Workflow.Gateway.PublishWorkflowEventAsyncResult.End(IAsyncResult result)
at Microsoft.Workflow.Gateway.WorkflowServiceGateway.ExceptionFilter[T](Func`1 body)   at System.Threading.Tasks.TaskFactory`1.FromAsyncCoreLogic(IAsyncResult iar, Func`2 endFunction, Action`1 endAction, Task`1 promise, Boolean requiresSynchronization)

Issue 7:
Instance is suspended instead of terminated if a non-transaction (Http) work item throws an exception.

Issue 8:
Enables WFM client to let you control retry behavior on http requests. You can add retry logic in the error handler on publish notification failures in WFM client.

Issue 9:
Fixes the Set-WFCertificate command to enable users to update outgoing signing certificate thumbprint in WFM farm configuration.

Issue 10:
Fixes transaction leaks that are caused by stored procedure execution failures on workflow manager front end when you publish a workflow.

Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors

Hello All,

After many performance issue investigations,  we have released at 10th of October 2015  following kb article for about “Outdated database statistics decrease SharePoint Server performance, cause time-outs, and generate run-time errors”
https://support.microsoft.com/en-us/kb/3103194

In this article scope we make availability and  some flexiblity for database maintenance operations about  preventing “outdated update statistics” for DBAs , and now you are not depending just only SharePoint Daily Timer job which responsible update database statistics by using the proc_updatestatistics SQL procedure anymore.

Our TechNet article “Best practices for SQL Server in a SharePoint Server farm” has now been updated with the same guidance and cross referencing the new KB article.

Do not enable auto-create statistics on a server that hosts SQL Server and SharePoint Server. Enabling auto-create statistics is not supported for SharePoint Server. SharePoint Server configures the required settings during provisioning and upgrade. Manually enabling auto-create statistics on a SharePoint database can significantly change the execution plan of a query. We recommend updating the SharePoint content database statistics daily using the FULLSCAN option from SQL Server. Although SharePoint does have a timer job to update statistics by calling proc_updatestatistics, we strongly recommend implementing a scheduled maintenance plan from SQL Server to ensure database statistics are reliably updated on a daily basis. For more information, see Outdated database statistics.

Best practices for SQL Server in a SharePoint Server farm
https://technet.microsoft.com/en-us/library/hh292622.aspx

Now ; to prevent  potential service outages, SQL Server maintenance plans can be implemented to keep SharePoint content database statistics updated by using the FULLSCAN option and it can be done manually by DBAs

When implementing the SQL Server maintenance plan to update the statistics on your SharePoint databases, it is not required to disable the job from SharePoint. However, because these maintenance tasks perform similar functions from both locations, it is permissible to disable the timer job from the SharePoint farm.

Cleaning orphan database from SharePoint Farm

The issue:

You have patched your Sharepoint farm but if you run the configuration wizard and it is always failing on step 9 /10 on upgrade Operations and you have facing following error:

SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy60        DEBUG        Web application [SPWebApplication Name=ContosoDB] has 1 IisSettings object        1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:01.93        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy61        DEBUG        Searching for sites with Server Comment = ContosoDB 1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:02.36        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPWebApplicationSequence        ajy66        DEBUG        Adding a iis web site object with instance Id = [319138241]        1a71389d-7618-d087-2401-036280754f67

10/17/2015 11:23:02.36        OWSTIMER (0x1920)        0x1614        SharePoint Foundation Upgrade        SPHierarchyManager        ajyw5        ERROR        Attempt to register null pointer at:    at Microsoft.SharePoint.Upgrade.SPHierarchyManager.AddNextLevelObjects(Object current, IEnumerable nextObjects)     at Microsoft.SharePoint.Upgrade.SPWssWebApplicationSequence.AddNextLevelObjects()     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, Boolean bRecursing, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPHierarchyManager.Grow(SPTree`1 root, SPDelegateManager delegateManager)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.ReflexiveUpgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Upgrade.SPUpgradeSession.Upgrade(Object o, Boolean bRecurse)     at Microsoft.SharePoint.Administration.SPPersistedUpgradableObject.Upgrade(Boolean recursively)     at

Then you have checked that ContosoDB which is not present in SQL server but why are you getting this error with that database . This is an orphan database record issue . SharePoint has still have information about that database the record about that database present in SharePoint configuration database (inside the Object table) but in real there is no database present in SQL server with that name.

Ok how can you verify this;
First find the related Web Application Id by powershell
$wa = Get-SPWebApplication -Identity http://contoso.com
$wa.Id

Then we need to check in SQL server by running some sql queries to get xml configuration of this web Application object:

SELECT TOP [Id],[ClassId],[ParentId],[Name],[Status],[Version],cast([Properties] as XML)
FROM [SharePoint_Config].[dbo].[Objects] where Id = ‘<Guid of the Web Application>’

And if you look in details for the related XML

OrphanRecordXml

You have verifed that if there is <fld type=”null” /> record in that xml.
Also we know that the deleted database’s id is 44c186d2-6581-4314-90bd-a00f3efe18e3

If you search for this ID in configuration database’s Object table for this content database;you will not find any record about it ;So it is an oprhan item.

Ok so far so good , I can hear your question that how we get rid off this with a supported way  ?
– Well you shouldnt change SharePoint databases manually , it is not supported by Microsoft .

Resolution is easy but It is a little bit tricky.
To remove that oprhan database we will run a single command.
We need the Web Application object in powershell
$wa = Get-SPWebApplication -Identity http://contoso.com
Then run this
$wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)

The tricky part when you run this you will get an error ! Upss did i something wrong , NO!

PS C:\Users\SPFarmAdmin> $wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)
Exception calling “Delete” with “1” argument(s): “Object reference not set to an instance of an object.”
At line:1 char:1
+ $wa.ContentDatabases.Delete(“44c186d2-6581-4314-90bd-a00f3efe18e3”)
+ CategoryInfo      : NotSpecified: (:) [], MethodInvocationException
+ FullyQualifiedErrorId : NullReferenceException

 Well after run this command , just ignore the error .Normally it fixes the configuration database and cleans the orphan record .
Not sure ??
Check yourself again the XML configuration of the Web Application from SQL server by following SQL again.
SELECT TOP [Id],[ClassId],[ParentId],[Name],[Status],[Version],cast([Properties] as XML)
FROM [SharePoint_Config].[dbo].[Objects] where Id = ‘<Guid of the Web Application>’

if you do not see the orphan   <fld type=”null” /> , then you good to go 🙂 You can run the configuration wizard again .