Share via

SharePoint DeadLock high CPU\Memory usage caused by SQL server

  • Article

This article illustrates how to troubleshoot a resource Deadlock by way of a real world example issue.

The log files show an error:

Exception returned from back end service. System.ServiceModel.ServiceActivationException: The requested service, 'http://SPserver:32843/cebdf111573e42e68f08f730c2273460/MetadataWebService.svc' could not be activated. See the server's diagnostic trace logs for more information.    Server stack trace:      at System.ServiceModel.Channels.HttpChannelUtilities.ValidateRequestReplyResponse(HttpWebRequest request, HttpWebResponse response, HttpChannelFactory1 factory, WebException responseException, ChannelBinding channelBinding)     at System.ServiceModel.Channels.HttpChannelFactory1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)     at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)     at System.ServiceModel.Channels.Securit...     0e0e6254-6880-426a-97ce-03d3bc471eba
...yChannelFactory1.SecurityRequestChannel.Request(Message message, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.SharePoint.Taxonomy.IMetadataWebServiceApplication.GetServiceSettings(Guid rawPartitio...  0e0e6254-6880-426a-97ce-03d3bc471eba
...nId)     at Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplicationProxy.<>c__DisplayClass32.<ReadApplicationSettings>b__31(IMetadataWebServiceApplication serviceApplication)     at Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplicationProxy.<>c__DisplayClass2f.<RunOnChannel>b__2d() 0e0e6254-6880-426a-97ce-03d3bc471eba
Exception returned from back end service. System.ServiceModel.ServiceActivationException: The requested service, '<a href="https://mex07a.emailsrvr.com/owa/redir.aspx?C=KRbZSeF9uU29V-5zxS1tsTPNGKdIwdFIXZhCLbBay6Qs_9lUFUpcvQ2u__5EYKOJ_IUe70_2VYM.&amp;URL=http%3a%2f%2fsynhnjseza1521%3a32843%2fcebdf111573e42e68f08f730c2273460%2fMetadataWebService.svc" target="_blank">http://SPserver:32843/cebdf111573e42e68f08f730c2273460/MetadataWebService.svc</a>' could not be activated. See the server's diagnostic trace logs for more information.    Server stack trace:      at System.ServiceModel.Channels.HttpChannelUtilities.ValidateRequestReplyResponse(HttpWebRequest request, HttpWebResponse response, HttpChannelFactory1 factory, WebException responseException, ChannelBinding channelBinding)     at System.ServiceModel.Channels.HttpChannelFactory1.HttpRequestChannel.HttpChannelRequest.WaitForReply(TimeSpan timeout)     at System.ServiceModel.Channels.RequestChannel.Request(Message message, TimeSpan timeout)     at System.ServiceModel.Channels.Securit...     81aba100-3f92-4c5b-9d20-53923c8db760
...yChannelFactory1.SecurityRequestChannel.Request(Message message, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannel.Call(String action, Boolean oneway, ProxyOperationRuntime operation, Object[] ins, Object[] outs, TimeSpan timeout)     at System.ServiceModel.Channels.ServiceChannelProxy.InvokeService(IMethodCallMessage methodCall, ProxyOperationRuntime operation)     at System.ServiceModel.Channels.ServiceChannelProxy.Invoke(IMessage message)    Exception rethrown at [0]:      at System.Runtime.Remoting.Proxies.RealProxy.HandleReturnMessage(IMessage reqMsg, IMessage retMsg)     at System.Runtime.Remoting.Proxies.RealProxy.PrivateInvoke(MessageData& msgData, Int32 type)     at Microsoft.SharePoint.Taxonomy.IMetadataWebServiceApplication.GetServiceSettings(Guid rawPartitio...  81aba100-3f92-4c5b-9d20-53923c8db760
...nId)     at Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplicationProxy.<>c__DisplayClass32.<ReadApplicationSettings>b__31(IMetadataWebServiceApplication serviceApplication)     at Microsoft.SharePoint.Taxonomy.MetadataWebServiceApplicationProxy.<>c__DisplayClass2f.<RunOnChannel>b__2d() 81aba100-3f92-4c5b-9d20-53923c8db760

When try to browse "'http://SPserver:32843/cebdf111573e42e68f08f730c2273460/MetadataWebService.svc'" I got message which indicate resources are not available. I found that CPU usage on server is 80 % and RAM usage 95 %. SQL server alone was using 9 GB out of 12 GB.

Cause: SQL server has a default behavior to not release RAM after it is consumed. It stores recent transaction in RAM to increase server performance. But if you have other application running on same machine then those application(e.g. SharePoint) will not get resource to perform their operation hence causing a deadlock. Soon due to repeated request from the server effect SQL server and we start getting error "SqlError: 'A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections."

Solution: It is a permanent solution to put a cap on SQL server RAM\Memory utilization. To do so follow below steps:

  1. Open SQL server management Studio > Connect to SQL server instance. You can connect it using SQL or windows credentials
    http://freeit-support.com/wp-content/uploads/2014/10/0072-300x216.jpg

  2. Right Click server go to Properties
    http://freeit-support.com/wp-content/uploads/2014/10/0073-176x300.jpg

  3. Select Memory

  4. For Max server Memory (in MB) Set Value which is about 40 % of total Memory available. I set it to 3500
    http://freeit-support.com/wp-content/uploads/2014/10/0074-300x179.jpg

  5. Click OK

  6. Right Click server and select restart
    http://freeit-support.com/wp-content/uploads/2014/10/0075-138x300.jpg

  7. You will get 2 prompt to stop and start the service. Click on Yes(Incase of SQL server 2012, you will get only 1 prompt to restart instead of 2)
    http://freeit-support.com/wp-content/uploads/2014/10/0071-300x60.jpg

Note: This will make sure SQL does not use more than assigned memory even if it is available. This is recommended only for environment which as DATABASE and Other APPLICATION on same system.

See Also