locked
Problem with MSCRMAsyncService RRS feed

  • Question

  • Hello!

    For some time past in Application Logs I faced the following errors:

    Host... a database operation failed while processing organization d67f7a7f-d5fc-491f-afcb-16ed3d90df3e. Will retry 12 times. Exception: System.Data.SqlClient.SqlException: Transaction (Process ID 82) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.
    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
    at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
    at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
    at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
    at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
    at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
    at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
    at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
    at Microsoft.Crm.CrmDbConnection.InternalExecuteNonQuery(IDbCommand command)
    at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command, Boolean impersonate)
    at Microsoft.Crm.CrmDbConnection.ExecuteNonQuery(IDbCommand command)
    at Microsoft.Crm.Asynchronous.DataAccessSharedBase.ExecuteSqlCommand(IDbCommand command, Nullable`1 expectedAffectedRecords)
    at Microsoft.Crm.Asynchronous.QueueDataAccess.UpdateStateAndStatusInternal(Guid eventId, Int32 newState, Int32 newStatus, DateTime nextOccurrence, Boolean retryOperation, Int32 currentRetryCount, Int32 errorCode, String errorMessage, Boolean clearData, Boolean removeEvent, Int32 operationType)
    at Microsoft.Crm.Asynchronous.QueueDataAccess.UpdateStateAndStatusCommand.InternalExecute()
    at Microsoft.Crm.Asynchronous.QueuedDatabaseCommand.Execute(QueueManager queueManager)

    The reason why errors occur I do not understand.
    The system uses a cluster scheme of servers. Can incorrect settings of clusters be the cause of the problem?
    Please help me out!

    Thank you.
    Saturday, April 18, 2009 1:14 PM

Answers

  • Hi,

    If you are using SQL Server on multi-core processors, the followings are suggested to configure in SQL server :

    1.    Set Isolation Level

    2.    Disable support for Parallel Plan Query

     

    Minimizing Locking Contention by Using Isolation Levels

    Locking contention can adversely impact the performance of Microsoft SQL Server. In service scheduling scenarios, to minimize locking contention while protecting transactions from “dirty” reads of uncommitted data modifications, use either:

    §  READ COMMITTED isolation with the READ_COMMITTED_SNAPSHOT database option set to ON.

    §  SNAPSHOT isolation

    Important: Using these isolation levels can also require higher levels of server processing to maintain the row versions in the temp database. Use server sizing as necessary to offset any impact on performance.

    READ COMMITTED Isolation

    READ COMMITTED isolation specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data. This option is the SQL Server default.

    With READ_COMMITTED_SNAPSHOT set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

    SNAPSHOT Isolation

    SNAPSHOT isolation specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

    Note: For more information about minimizing locking contention by using isolation levels, in SQL Server Books Online, view the following resources:

    §  SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
    http://msdn2.microsoft.com/en-us/library/ms173763.aspx

    §  Adjusting Transaction Isolation Levels
    http://msdn2.microsoft.com/en-us/library/ms189542.aspx

    §  Customizing Transaction Isolation Level
    http://msdn2.microsoft.com/en-us/library/ms175909.aspx

     

    Disabling Support for Parallel Plan Queries

    On computers with multiple processors, Microsoft SQL Server determines the optimal number of processors, degree of parallelism, required to run a single statement, for each parallel plan execution. Administrators can use the ‘max degree of parallelism’ option to limit the number of processors to use in parallel plan execution.

    In a default configuration of Microsoft SQL server, the ‘max degree of parallelism’ value is set at 0, which specifies to use all available processors. Setting this value to 1 will suppress parallel plan generation, while setting the value to a number greater than 1 (up to a maximum of 64) will restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the ‘max degree of parallelism’ value is ignored.

    This setting can be changed by using SQL Server Management Studio, or using the sp_configure system stored procedure.

    Important: The ‘max degree of parallelism’ option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change ‘max degree of parallelism’ only when ‘show advanced options’ is set to 1. The setting takes effect immediately (without restarting the MSSQLSERVER service). For more information, in SQL Server Books Online, see the topic max degree of parallelism Option:

    http://msdn2.microsoft.com/en-us/library/ms181007.aspx

    For more information about setting the ‘max degree of parallelism’ value in SQL Server Management Studio, see How to: Configure the Number of Processors Available for Parallel Queries (SQL Server Management Studio):

    http://msdn2.microsoft.com/en-us/library/ms189094.aspx


    Cheers!
    • Proposed as answer by Ye Wint Wednesday, May 6, 2009 3:11 PM
    • Marked as answer by Donna EdwardsMVP Thursday, May 14, 2009 11:06 PM
    Tuesday, April 21, 2009 8:45 AM
  • also you can add the OLEDB timeouts to the MSCRM directory in the registry
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Tuesday, April 21, 2009 5:15 PM

All replies

  •  Hi Camena,

    As per my understanding you are using the NLB Clustering at web servers.

    So when u r hitting the application it happens that NLB Resolves the application URL and takes you to a specific node internally.

    You need to check that which node of ur web server is getting struck by Asynch service.

    I dont see any thing related to Asynch serice failure with Clusters.

    And from the error it seems that this error node was unable to connect to the sql box from this server.Can you try to hit the sql box from both the Clustered Web boxes.

    And unless we know the exact reason when the Asynch service is getting struck we cant say the proper solution.Even though many ppl suggested Rollup 2/3.

    But again we need to know the rootcause to apply them inorder to fix the issue.

     

    Thanks,

    Arif


    Mohammed Arif
    Monday, April 20, 2009 9:14 PM
  • Hi,

    If you are using SQL Server on multi-core processors, the followings are suggested to configure in SQL server :

    1.    Set Isolation Level

    2.    Disable support for Parallel Plan Query

     

    Minimizing Locking Contention by Using Isolation Levels

    Locking contention can adversely impact the performance of Microsoft SQL Server. In service scheduling scenarios, to minimize locking contention while protecting transactions from “dirty” reads of uncommitted data modifications, use either:

    §  READ COMMITTED isolation with the READ_COMMITTED_SNAPSHOT database option set to ON.

    §  SNAPSHOT isolation

    Important: Using these isolation levels can also require higher levels of server processing to maintain the row versions in the temp database. Use server sizing as necessary to offset any impact on performance.

    READ COMMITTED Isolation

    READ COMMITTED isolation specifies that statements cannot read data that has been modified but not committed by other transactions. This prevents dirty reads. Data can be changed by other transactions between individual statements within the current transaction, resulting in non-repeatable reads or phantom data. This option is the SQL Server default.

    With READ_COMMITTED_SNAPSHOT set to ON, the Database Engine uses row versioning to present each statement with a transactionally consistent snapshot of the data as it existed at the start of the statement. Locks are not used to protect the data from updates by other transactions.

    SNAPSHOT Isolation

    SNAPSHOT isolation specifies that data read by any statement in a transaction will be the transactionally consistent version of the data that existed at the start of the transaction. The transaction can only recognize data modifications that were committed before the start of the transaction. Data modifications made by other transactions after the start of the current transaction are not visible to statements executing in the current transaction. The effect is as if the statements in a transaction get a snapshot of the committed data as it existed at the start of the transaction.

    Note: For more information about minimizing locking contention by using isolation levels, in SQL Server Books Online, view the following resources:

    §  SET TRANSACTION ISOLATION LEVEL (Transact-SQL)
    http://msdn2.microsoft.com/en-us/library/ms173763.aspx

    §  Adjusting Transaction Isolation Levels
    http://msdn2.microsoft.com/en-us/library/ms189542.aspx

    §  Customizing Transaction Isolation Level
    http://msdn2.microsoft.com/en-us/library/ms175909.aspx

     

    Disabling Support for Parallel Plan Queries

    On computers with multiple processors, Microsoft SQL Server determines the optimal number of processors, degree of parallelism, required to run a single statement, for each parallel plan execution. Administrators can use the ‘max degree of parallelism’ option to limit the number of processors to use in parallel plan execution.

    In a default configuration of Microsoft SQL server, the ‘max degree of parallelism’ value is set at 0, which specifies to use all available processors. Setting this value to 1 will suppress parallel plan generation, while setting the value to a number greater than 1 (up to a maximum of 64) will restrict the maximum number of processors used by a single query execution. If a value greater than the number of available processors is specified, the actual number of available processors is used. If the computer has only one processor, the ‘max degree of parallelism’ value is ignored.

    This setting can be changed by using SQL Server Management Studio, or using the sp_configure system stored procedure.

    Important: The ‘max degree of parallelism’ option is an advanced option. If you are using the sp_configure system stored procedure to change the setting, you can change ‘max degree of parallelism’ only when ‘show advanced options’ is set to 1. The setting takes effect immediately (without restarting the MSSQLSERVER service). For more information, in SQL Server Books Online, see the topic max degree of parallelism Option:

    http://msdn2.microsoft.com/en-us/library/ms181007.aspx

    For more information about setting the ‘max degree of parallelism’ value in SQL Server Management Studio, see How to: Configure the Number of Processors Available for Parallel Queries (SQL Server Management Studio):

    http://msdn2.microsoft.com/en-us/library/ms189094.aspx


    Cheers!
    • Proposed as answer by Ye Wint Wednesday, May 6, 2009 3:11 PM
    • Marked as answer by Donna EdwardsMVP Thursday, May 14, 2009 11:06 PM
    Tuesday, April 21, 2009 8:45 AM
  • also you can add the OLEDB timeouts to the MSCRM directory in the registry
    Tiaan van Niekerk http://crmdelacreme.blogspot.com Skype:tiaan.van.niekerk1
    Tuesday, April 21, 2009 5:15 PM