SQL timeout expired

(0) Share

Report

Posted on by Community Member

On creation on new contract entity users sporadically experiencing the following error

Unhandled Exception: System.ServiceModel.FaultException`1[[Microsoft.Xrm.Sdk.OrganizationServiceFault, Microsoft.Xrm.Sdk, Version=5.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35]]: SQL timeout expired.Detail:

<OrganizationServiceFault xmlns:i="www.w3.org/.../XMLSchema-instance" xmlns="schemas.microsoft.com/.../Contracts"> <ErrorCode>-2147204783</ErrorCode> <ErrorDetails xmlns:d2p1="schemas.datacontract.org/.../System.Collections.Generic" /> <Message>SQL timeout expired.</Message> <Timestamp>2014-03-06T09:03:36.895599Z</Timestamp> <InnerFault i:nil="true" /> <TraceText i:nil="true" /> </OrganizationServiceFault>

On the web server on the exact time (up to second) I found the following warning

Query execution time of 30.1 seconds exceeded the threshold of 10 seconds. Thread: 69; Database: Organisationname_MSCRM; Server:SomeServer; Query: declare @currentval int update OrganizationBase set @currentval = CurrentContractNumber, CurrentContractNumber = CurrentContractNumber + 1 where OrganizationId = '05282613-2e3a-4659-950b-e4d7b8edfef9' select @currentval

Now it is becoming headache users are experiencing frequently 6 or 8 times a day, similar warning available in log when creating incident but that was not shown in the user interface.

Server version: On Premise CRM 2011 (Rollup 12)

Any ideas or suggestion?

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Community Member on at

Like (0)

Report

Try going over this information.

community.dynamics.com/.../sql-timeouts-in-crm-generic-sql-error.aspx

But it also can be on the SQL server side, with index issues and large transaction logs with thousands of VLFs.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks cjspanburgh!

I did that in my test environment still I am getting errors. One more observation I would like make is same SQL warning is also coming for when I create an Incident but that is not showing up in the form (user interface). This error is random not all creation of Contract is showing the error on UI.

Thanks,

Pavan

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

If you are saving long running queries to the log file and logging odbc system DSN stats to a log file check them.

In the past I have seen inconsistent authentication to the service attributes in AD for the Server account object.

If your using TCP Sockets, you could have some authentication failures.

Use the SPN tool from MSFT to check if the SPNs of the Server object are being read, and check for inconsistent Kerberos authentication.

For instance I had a client that had intermittent failures in CRM and Dynamics GP. An issue with the TCP stack on the virtual NIC caused SQL errors. So at this level you could see issues effecting the Software.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks again!

Case 1

declare @currentval int

update OrganizationBase set @currentval = CurrentContractNumber, CurrentContractNumber = CurrentContractNumber + 1 where OrganizationId = '05282613-2e3a-4659-950b-e4d7b8edfef9' select @currentval

Case 2:

declare @currentval int

update OrganizationBase set @currentval = CurrentCaseNumber, CurrentContractNumber = CurrentCaseNumber + 1 where OrganizationId = '05282613-2e3a-4659-950b-e4d7b8edfef9'

select @currentval

Only on Case 1 random errors which are appearing on especially user interface is the main concern, only when creating contract errors appearing to end user screen. All it is doing updating auto number (case number or contract number) setting by internal hidden plug in. OrganizationBase table has only one row surely indexing is out of the picture. This table getting updated when you create incident or contract. Details of this table can be viewed in user interface by admin by going Settings → Administraiton → System Settings.

Failing Kerbos, TCP stack any other failures can not happen only when creating contract record.

Why it is taking more then 10 seconds to update a field one row year 2014 on hardware 4 core 64bit processor with 8GB RAM is really baffling, reminds me famous quote from Weinberg's:

"If builders built buildings the way programmers wrote programs, then the first woodpecker that came along would destroy civilization."

:-)

Was this reply helpful? Yes No
Suggested answer

Nina P. 2,195 on at

Like (0)

Report

Hi Pavan

I would like to recommend you the below action plan which will help you/us better understand the current state of the issue and troubleshoot the issue:

1. Run the Microsoft Diagnostics Package and apply recommendations if any Microsoft Dynamics CRM Baseline Information Collector for Windows Server 2008 R2 home.diagnostics.support.microsoft.com/SelfHelp

This tool collects information that is used to troubleshoot common Microsoft Dynamics CRM issues.

2. Collect the below information

• Are you able to always reproduce the issue by demand?

• Does the issue occur at particular time during the day?

• Is there any further pattern when the issue occurs which you have identified?

• How many users are affected by the issue?

• How often the issue does occur?

• Try to reproduce the problem on a different client machine with different user account if not yet done

• Do you have some other jscipts or plugins running for the affected entities than the reported one?

• Have you been able to use this plugin before without experiencing this issues?

3. Once you have identified/ confirmed a repro scenario test the same from a different client machine with different user account and from a different network if possible

4. Run the below Diagnostics Tool 2 times - from a moment when the issue doesn’t occur and second time while you are running the identified scenario to ensure that the required network condtions are good.

5. Run the Diagnostics tool:

1. Log in to your CRM Online organization as a user in an Admin role.

2. Navigate to the appropriate relative path for your organization.

http://<serverUrl>/tools/diagnostics/diag.aspx for Onpremise

6. Click the Run button for All Tests.

The client latency tool provides insight into the network conditions you are working with. What we’ve been given as a general guideline from our product team is that performance will start to degrade at:

• Latency > 150 ms

• Bandwidth < 50 KB/sec

7. Collect Platform and SQL Profiler Traces while reproducing the issue

8. Analyze the traces, identify the affected query and analyze the query in Database Engine Tuning Advisor. Analyze and apply recommendations if any

Please let us know if this helps.

Thank you for using Microsoft Dynamics CRM Communities.

Nina Peneva

Support Engineer

Microsoft Dynamics CRM

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

With little bit luck we have narrow down the issue using almost identical steps as you suggested.

Issue happening randomly when users creating a new Contract, happening when different users when they are creating new contract.

I did check the logs on both (web and sql) , when the users experienced this error i have captured this SQL trace + windows event (see below) including upto exact second.

When contract created this it is updating OrganizationBase table by incrementing +1 (which is standard procedure and out of the box to do) to update only 1 row (thats in that table) SQL server taking with 64bit processor 8GB RAM is taking more 30 seconds.

My guess here is when it is updating it is locking the row and causing this error (pure speculation) here is my logs

Here is log from Web server,

Warning dd/mm/yyyy 12:39

MSCRMPlatform

17972

None

Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 23; Database: Org_MSCRM; Server:BigServerXXXX;

Query: sp_getapplock.

Warning

dd/mm/yyyy 12:39

MSCRMPlatform

17972

None

Query execution time of 30.0 seconds exceeded the threshold of 10 seconds. Thread: 23; Database: Org_MSCRM; Server:BigServerXXXX;

Query: sp_getapplock.

SQL server here trace log on exact

<Event id="10" name="RPC:Completed">

<Column id="11" name="LoginName">SmallOrg\XXXAdminXRM</Column>

<Column id="15" name="EndTime"></Column>

<Column id="12" name="SPID">105</Column>

<Column id="16" name="Reads">2</Column>

<Column id="1" name="TextData">exec sp_executesql N'declare @currentval int

update OrganizationBase set @currentval = CurrentContractNumber, CurrentContractNumber = CurrentContractNumber + 1 where OrganizationId = @orgid

select @currentval',N'@orgid uniqueidentifier',@orgid='xxxxxxxx-xxxx-xxx-xxxx-xxxxxxxxxxxx'</Column>

<Column id="9" name="ClientProcessID">14976</Column>

<Column id="13" name="Duration">261</Column>

<Column id="17" name="Writes">0</Column>

<Column id="2" name="BinaryData">00000000040000001A00730070005F006500780065006300750074006500730071006C009E01000082001A00E7206E00760061007200630068006100720028003100380037002900760100006400650063006C0061007200650020004000630075007200720065006E007400760061006C00200069006E0074000A0075007000640061007400650020004F007200670061006E0069007A006100740069006F006E004200610073006500200073006500740020004000630075007200720065006E007400760061006C0020003D002000430075007200720065006E00740043006F006E00740072006100630074004E0075006D006200650072002C002000430075007200720065006E00740043006F006E00740072006100630074004E0075006D0062006500720020003D002000430075007200720065006E00740043006F006E00740072006100630074004E0075006D0062006500720020002B002000310020007700680065007200650020004F007200670061006E0069007A006100740069006F006E004900640020003D00200040006F0072006700690064000A00730065006C0065006300740020004000630075007200720065006E007400760061006C005400000082001800E7206E00760061007200630068006100720028003200330029002E00000040006F007200670069006400200075006E0069007100750065006900640065006E007400690066006900650072004800000048002000241075006E0069007100750065006900640065006E007400690066006900650072000C0040006F007200670069006400248A4AD661908F4A81D28BD2ECF449191400000003000600380469006E00740000000000</Column>

<Column id="6" name="NTUserName">XXXAdminXRM</Column>

<Column id="10" name="ApplicationName">.Net SqlClient Data Provider</Column>

<Column id="14" name="StartTime"></Column>

<Column id="18" name="CPU">0</Column>

</Event>

Thanks

Pavan

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Hi Pavan

Did you find an answer to thsi eventually? We have an identical problem - 30 secs to update and causing sql timeouts. Any light you can shed would be greatly appreciated

Simon

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

We were encountering a similar issue on CRM 2013 (SP1) and I think we might have solved it.

In our case it was because of a coding error inside a custom workflow activity, which is set to run synchronously after entity creation.

The symptom was that it'd timeout on every 2nd new entity being created. The 1st one is always fine. Every 2nd and subsequent creations would timeout.

It might be worthwhile checking if you have custom workflow activities or plugins running on your entities, and eliminate them as a potential cause by disabling them.

Was this reply helpful? Yes No