Monday, March 11, 2013

Microsoft System Center Operations Manager (SCOM) 2012 - Event 29181 for Store Index Optimize work item - How to find such rules running

Hi Guru's,

I recently worked on another SCOM 2012 case and below is a summary of it.

Problem:

Event 29181 generating in OM log for SQL exception related to below two workflows.

• Configuration Store index optimization work item
• Configuration Store statistics update work item 

So, How to find 2 workflow which thrown an exception with event 29181, a question came up in mind that how to check scheduled rule that is running the indexing and stats update
These rules running every hourly and creating huge DB traffic however unable to view from console.


Error Message :
Description: Management Configuration Service group failed to perform Configuration Store index optimization work item for a period of time. Last error message (if available): 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.IndexOptimizeWorkItem.ExecuteSharedWorkItem()    at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem() ----------------------------------- System.Data.SqlClient.SqlException (0x80131904): Sql execution failed. Error 2801, Level 16, State 1, Procedure ServerAuditTrigger, Line 34, Message: The definition of object ServerAuditTrigger has changed since it was compiled.    at System.Data.SqlClient.SqlConnection.OnError(SqlException


Description: Management Configuration Service group failed to perform Configuration Store statistics update work item for a period of time. Last error message (if available): 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.StatisticsUpdateWorkItem.ExecuteSharedWorkItem()    at Microsoft.EnterpriseManagement.ManagementConfiguration.Interop.SharedWorkItem.ExecuteWorkItem() ----------------------------------- System.Data.SqlClient.SqlException (0x80131904): Sql execution failed. Error 2801, Level 16, State 1, Procedure ServerAuditTrigger, Line 34, Message: The definition of object ServerAuditTrigger has changed since it was compiled.    at System.Data.SqlClient.SqlConnection.OnError(SqlException



Here, Followed step by step action plan as below.

Action plan1 :
First thing can look at powershell, lets see example of Heartbeat Health service,  can use below command to get results.

can use PowerShell or read MP files xml and dig through the file to find the monitor or alert’s ID.  
Open Operations Manager Shell and run this command. 
get-alert -criteria 'Name = ''Health Service Heartbeat Failure''' 
This will work if you have alerts that are available in the management group. 
The data returned will have a line for MonitoringRuleId.  This is the value you can specify in the AlertSource section for Id. 
MonitoringRuleId : b59f78ce-c42a-8995-f099-e705dbb34fd4  
What if do not have any recent alerts that can query with PowerShell to find the Id?  get-monitor -criteria 'DisplayName = ''Health Service Heartbeat Failure''' 
Id: b59f78ce-c42a-8995-f099-e705dbb34fd4  You can use similar command line for rules by using get-rule command.  To get the GroupIdFilter you can use PowerShell to get the Group ID similarly. 
get-monitoringobjectgroup  Find the group you want and find the associated Id. 
Id: 9e249559-e166-0e92-1bfc-fea90a63f843 
There is an article for 2007 version,   http://blogs.technet.com/b/stefan_stranger/archive/2010/11/30/what-monitors-rules-and-discoveries-are-running-on-an-opsmgr-agent.aspx  

Then observed it shows all monitors and rules running but still not those 2 trying to find on which SQL exception thrown.

Action Plan 2:
Then tried to look at this article to get the info for all the things running on server or an agent. (Use simply reporting feature) 
http://technet.microsoft.com/en-us/library/hh916937.aspx  

however it will display all the rules or monitors running executed by MP or from console, not a system hard coded.

Action Plan 3:
Then try to look from console itself by following below steps.
To view all rules and monitors running on a computer 
1. Open the Operations console and click Monitoring. 
2. For an agent-managed computer, navigate to the Operations Manager\Agent Details\Agent Health State view.  For a management server, navigate to Operations Manager\Management Server\Management Servers State view. 
3. Click the agent you want to see rules and monitors for. 
4. In the Tasks pane, select the task Show Running Rules and Monitors for this Health Service. 
5. The Run Task – Show Running Rules and Monitors for this Health Service dialog box appears. Click Run.  
6. The Task Status dialog box appears. When the task is completed, you can click Copy Text or Copy HTML and paste the task output in the appropriate tool for analysis

Resolution:

Finally conclusion is, below 2 rules are hard coded into system and by default it will execute every hour to run against each Database.

• Configuration Store index optimization work item
• Configuration Store statistics update work item 

This is the default configuration.   
The work item ones just optimize the configuration and the “OptimizeIndex” one optimizes the dataset in OperationsManager DB. 

When we start the System Center Management Configuration service, the config service engine work item will be loaded. (CS.WorkItemDescriptorList)   
They will be processed one by one on the dirty agents (config file out-of-date).
When the former completed, the next will be got by CS.WorkItemGetNext.   
All the interval time for the jobs are hard coded. We don’t support to modify them.  CS.IndexOptimize and CS.IndexOptimize are the exact ones in this case.   
You can find the details under OperationsManager database. 


So, to Summurize,

Problem

How to find the work items from configuration service 

Solution

CS.StatisticsUpdate and CS.IndexOptimize are the configuration store work items.
If the work items are not processed successfully, it will still exist in the CS.workitem table and workitemstateID =1. Other management server will pick this item up and process again until it is successfully processed and workitemstateID marked as 20. 


If the work items will be finally processed, we can ignore the failure events.   

More Info

Tune the configurations of work items  http://blogs.technet.com/b/momteam/archive/2013/01/29/support-tip-config-service-deltasynchronization-process-fails-with-timeout-exception.aspx 

In fact, we can actually have a configuration file to set the frequency of the work items executed.   
Its path is C:\Program Files\System Center 2012\Operations Manager\Server\ConfigService.config   
You can modify the value of the setting in this file. (You may need to stop the System Center Management Configuration service before the modification).   


 








Post a Comment