This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(124.80000000000001, 53%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EE%3C/text%3E%3C/svg%3E)
I'm investigating an issue with the DeltaSynchronization in a SCOM 2019 environment (already checked and adjusted the ConfigService.config with a higher timeout and checked the MAXDOP settings, with no avail), and was wondering if the compatibility level of the SQL databases could be the issue. The databases run on SQL 2016 SP2 CU16 but with compatibility level 'SQL Server 2012 (110)', possibly a remnant of previous upgrades (it started with SCOM 2012 R2 a long time ago). As the documentation suggests SCOM 2019 only supports SQL 2016 and up, but before I made such an impactful adjustment as changing the compatibility level, I thought to ask around.
Might this be something that has to be done for having a SCOM-supported SQL version, regardless of the issues we're having?
Throughout the weekend the DeltaSyncs have run smoothly again. Setting the compatibility level to the minimum required version (SQL 2016 - 130) seems to have done the job.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(233.6, 30%, 32%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ECM%3C/text%3E%3C/svg%3E)
@EvH , Thanks for the update. I am glad that the DeltaSyncs have run smoothly after setting the compatibility level to the minimum required version (SQL 2016 - 130) . Congratulations! If there's anything else we can discuss in the future, feel free to post in our Q&A.
Thanks for the sharing and have a nice day!
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(67.2, 1%, 16%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ETP%3C/text%3E%3C/svg%3E)
The compatibility level only affects very specific functions in SQL Server. It is unlikely changing the compatibility level will have any effect your your issue, positive or negative.
@EvH , For SCOM 2019, in our official article, if it hosts reporting Server, Data Warehouse, and ACS database, we recommend to consider upgrading SQL to 2016 and install the latest service Packs or higher version to avoid some unknown issues.
https://learn.microsoft.com/en-us/system-center/scom/plan-sqlserver-design?view=sc-om-2019#sql-server-requirements
We can schedule a time to do the upgrade and see if the issue will disappear.
Hope it can help.
If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.
Yes, I've seen that documentation and we already have SQL 2016 installed. It's merely a question about the compatibility level of 110 being supported.
@Tom Phillips , thanks for that. It might be an SQL issue as well, since the timeout we're getting is on the DeltaSync running between the production database and the datawarehouse database.
@EvH , Thanks for the reply.
After doing more research, I didn't find any official article describe that the unsupported compatibility level for SCOM 2019. with SQL 2016, 110 is one of the supported Compatibility level. So I think it is supported for our SCOM 2019.
https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-ver15#best-practices-for-upgrading-database-compatibility-level
For our DeltaSync issue, Could you let us know if we get any detailed error message?
@Crystal-MSFT , it's mainly about these events with ID 29181:
OpsMgr Management Configuration Service failed to execute 'DeltaSynchronization' engine work item due to the following exception
Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessException: Data access operation failed
at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperation.ExecuteSynchronously(Int32 timeoutSeconds, WaitHandle stopWaitHandle)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.ExecuteOperationSynchronously(IDataAccessConnectedOperation operation, String operationName)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.WorkItemCompleted(IConfigServiceEngineWorkItemHandle workItemHandle, IConfigServiceEngineWorkItemResult workItemResult)
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.ConfigServiceEngineWorkItem.Execute()
-----------------------------------
System.Data.SqlClient.SqlException (0x80131904): Sql execution failed. Error 50000, Level 16, State 1, Procedure WorkItemMarkCompleted, Line 61, Message: Failed to report work item completion. Work item with id 32992532 is not assigned to service instance 'SCOMSERVER\Default'
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at System.Data.SqlClient.SqlCommand.CompleteAsyncExecuteReader(Boolean isInternal, Boolean forDescribeParameterEncryption)
at System.Data.SqlClient.SqlCommand.InternalEndExecuteNonQuery(IAsyncResult asyncResult, String endMethod, Boolean isInternal)
at System.Data.SqlClient.SqlCommand.EndExecuteNonQueryInternal(IAsyncResult asyncResult)
at System.Data.SqlClient.SqlCommand.EndExecuteNonQuery(IAsyncResult asyncResult)
at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.NonQuerySqlCommandOperation.SqlCommandCompleted(IAsyncResult asyncResult)
ClientConnectionId:9d76e46d-8429-405b-adaa-ce1b46dc5ecf
Error Number:50000,State:1,Class:16
OpsMgr Management Configuration Service failed to execute 'LocalHealthServiceDirtyNotification' engine work item due to the following exception
Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException: Exception of type 'Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException' was thrown.
at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperation.ExecuteSynchronously(Int32 timeoutSeconds, WaitHandle stopWaitHandle)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.ExecuteOperationSynchronously(IDataAccessConnectedOperation operation, String operationName)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.IsAgentDirty(Guid agentId, LastAgentChangeCookie& lastAgentChangeCookie)
at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.LocalDirtyNotificationWorkItem.ExecuteWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.ConfigServiceEngineWorkItem.Execute()
OpsMgr Management Configuration Service failed to execute 'DirtyNotification' engine work item due to the following exception
Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException: Exception of type 'Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperationTimeoutException' was thrown.
at Microsoft.EnterpriseManagement.ManagementConfiguration.DataAccessLayer.DataAccessOperation.ExecuteSynchronously(Int32 timeoutSeconds, WaitHandle stopWaitHandle)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.ExecuteOperationSynchronously(IDataAccessConnectedOperation operation, String operationName)
at Microsoft.EnterpriseManagement.ManagementConfiguration.SqlConfigurationStore.ConfigurationStore.GetDirtyAgentList(Int32 maxAgentBatchSize)
at Microsoft.EnterpriseManagement.ManagementConfiguration.Engine.DirtyNotificationWorkItem.ExecuteSharedWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem()
at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.ConfigServiceEngineWorkItem.Execute()
When running the SQL query:
SELECT * FROM cs.WorkItem WHERE WorkItemName = 'DeltaSynchronization'
order by StartedDateTimeUtc desc
the results show WorkItemStateId 15 and 10 most of the time, with a couple of results with WorkItemStateId 20. So it does complete once in a while, but most of the time it simply times out and tries again 10 minutes later.
We already increased the "DefaultTimeoutSeconds" and "GetEntityChangeDeltaList" in the ConfigService.config file on all our management servers and also increased the "SOFTWARE\Microsoft\Microsoft Operations Manager\3.0\Data Warehouse\Command Timmeout Seconds" registry setting. The only effect visible is that the DeltaSyncs take a longer time to timeout.
@EvH , From the logs, I notice 'DeltaSynchronization', 'LocalHealthServiceDirtyNotification and 'DirtyNotification' work items are with timeout exception. And work item Failed to report work item completion.
I notice some work items are with WorkItemStateId 15 and 10. Based as I know, 10 means failed. 15 means timed out. I find we have changed "DefaultTimeoutSeconds" and "GetEntityChangeDeltaList". Could you let us know if we change the the value in the both locations:
After changing this on every management server, restart the OpsMgr Management Configuration Service to see if the issue can be fixed.
Yes, we did.
We also talked with our DBA'ers and figured that changing the compatibility level to 130 (SQL Server 2016) shouldn't hurt. So we did, on both databases, and the DeltaSyncs started running again with noticeably lower run times. Last night there was a period of time the timeouts were happening again, but eventualy they calmed down again and are running fine ever since.
We'll let this run for a few days and see where it'll take us.
@EvH , Thanks for the update. I am glad to hear that the DeltaSyncs perform better than before after changing compatibility level to 130. And we will monitor for some days. If there's any update, feel free to let us know.