Categories
Who's Online
5 visitors online now
2 guests, 3 bots, 0 members
Support my Sponsor

SharePoint DeadLock high CPU\Memory usage caused by SQL server

Yesterday there was a resource Dead lock in our environment. Here is the analysis of same.

In the log files I see below 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, HttpChannelFactory
1 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
...yChannelFactory
1.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
0072

  1. Right Click server go to Properties
    0073

  2. Select Memory

  3. For Max server Memory (in MB) Set Value which is about 40 % of total Memory available. I set it to 3500
    0074

  4. Click OK

  5. Right Click server and select restart
    0075
  6. 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)
    0071

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

Leave a Reply