KB: Service Manager 2010 Reports fail to include current data

[System-Center-Team]

First published on TECHNET on May 08, 2012

Here’s a new Knowledge Base article we published this morning. This one discusses an issue where reporting data isn’t current due to a hung MPSyncJob or DWMaintenance job.

=====

Symptoms

In System Center Service Manager 2010, you may experience one or more of the following:

-Reports do not have current information
-ETL jobs are not running
-The MPSyncJob or DWMaintenance job is running for an extended period of time (e.g. a more than a few hours)

Cause

This can occur when either the MPSyncJob or DWMaintenance job is hung or stuck.

NOTE When the MPSyncJob or DWMaintenance job is running, it disables the ETL job. Because of this, no ETL jobs can run until MPSyncJob or DWMaintenance job finishes running. When either job is hung, no data will move to the DWDatamart for reporting.

NOTE The MPSyncJob can take up to 6 hours for intial deployment. However after initial deployment the MPsync job should complete quickly, usually in a few minutes. If the MPSyncJob is taking several hours or longer that there is a problem that needs to be addressed. This expected time also applies to the DWMaitnenance job - more information on resolving those issues can be found at http://technet.microsoft.com/en-us/library/hh542403.aspx

Resolution

Before troubleshooting, you will first need to understand what a Batch, Module, and Workitem are.

Batch: Data Warehouse will create the batch for each process when it wants to run the extract job. For example:

Select * FROM Infra.Process Where ProcessName like '%Extract%'

Data Warehouse will create the batch for this process

S elect * FROM Infra.Batch Where ProcessId = <ProcessID from 1st query>

Module: Module is defined to get the data from classes. Multiple modules are bound together to run in batches. One module runs one Workitem. To find out the modules process that the 1st query is running, use the query below:

Select * FROM Infra.ProcessModule Where ProcessId = <ProcessID from 1st query>

Workitem: Workitem is the smallest execution unit. Once all workitems are finished then the batch will be considered as finished

Select * FROM Infra.WorkItem Where BatchId = <BatchID from 2nd query>

Compare this query result with the result of 3rd query result (number of rows returned by query) will be same as every module runs in separate Workitem.

Troubleshooting Steps:

MPSyncJob hung:

When we see that the MPSyncJob hung, we first need to figure out why it hung. You can do that using the steps below:

1. Get the MPSyncJob batchid using the PowerShell command Get-SCDWJob –JobName MPSyncJob.
2. Issue the query to DWStagingAndConfig using the batchid from step #1.

select * from infra.WorkItem where BatchId = '...'

3. If no workitem is in status 7 (i.e. all of the workitems are either 6 or 3), and the job has been running for a while, then we need to restart the “System Center Management” service.

NOTE Find all the available status by using the query below.

Select * FROM Infra.Status

4. Otherwise, check the CustomInfo column of workitems whose statusid = 7

If it says waiting for acquiring lock, that means currently the DWMaintenance job is running. Wait until that job is complete, then the focus on why the DWMaintenance job is hung. If it says waiting for deployment to complete, then we need to run the following:

select * from DeploySequenceView where DeploymentStatusId != 6

If the query returns something where the status is waiting or not started or running, that means the deployment cannot complete. Next we need to check if deployment and execution processcategory are enabled using he following query:

Select * from infra.ProcessCategory

From here, we want to make sure the Deploymen t and Execution process categories are enabled. If they are enabled, from this point on the investigation should focus on MP deployment. Change the underlying table using this query

select * from DeploySequenceView where DeploymentStatusId != 6

This only returns those with a failed status, or if there are MPs that depend on the failed MP, their status should be Waiting. From the view DeploySequenceView definition in DWStaingAndConfig, we can see deploymentstatusId is calculated via the following tables:

dbo.DeploySequence,dbo.DeploySequenceStaging,and Infra.WorkItem

The work around for this is to modify the deploysequencestaging table using the commands below.

NOTE Please complete a backup of the DWStagingAndConfig database before running the query below.

use DWStagingAndConfig
update DeploySequenceStaging
set StatusId = 2
where StatusId = 4

The work around above is just to allow the MPSyncJob job to move on; it doesn’t resolve the MP failure to deploy issue.

DWMaintenance job hung

In general, when we see the DWMaintenance job is hung, we would troubleshoot this much the same way we would a hung MPSyncJob:

Get the DWMaintenance batchid by using the PowerShell command Get-SCDWJob –JobName DWMaintenance

Issue the following query to DWStagingAndConfig using the batchid from first step:

select * from infra.WorkItem where BatchId = '...'

From here, continue on just as you would for a hung MPSyncJob.

More Information

Prevention:
Monitor the Jobs running status weekly using the PowerShell command Get-SCDWJob . Use PowerShell instead of the UI because from the PowerShell output you can see how long the job has been running.

=====

For the most current version of this article please see the following:

2703027 - Service Manager 2010 Reports fail to include current data

J.C. Hornbeck | System Center & Security Knowledge Engineer

Get the latest System Center news on Facebook and Twitter :

App-V Team blog: http://blogs.technet.com/appv/
ConfigMgr Support Team blog: http://blogs.technet.com/configurationmgr/
DPM Team blog: http://blogs.technet.com/dpm/
MED-V Team blog: http://blogs.technet.com/medv/
Orchestrator Support Team blog: http://blogs.technet.com/b/orchestrator/
Operations Manager Team blog: http://blogs.technet.com/momteam/
SCVMM Team blog: http://blogs.technet.com/scvmm
Server App-V Team blog: http://blogs.technet.com/b/serverappv
Service Manager Team blog: http://blogs.technet.com/b/servicemanager
System Center Essentials Team blog: http://blogs.technet.com/b/systemcenteressentials
WSUS Support Team blog: http://blogs.technet.com/sus/

The Forefront Server Protection blog: http://blogs.technet.com/b/fss/
The Forefront Endpoint Security blog : http://blogs.technet.com/b/clientsecurity/
The Forefront Identity Manager blog : http://blogs.msdn.com/b/ms-identity-support/
The Forefront TMG blog: http://blogs.technet.com/b/isablog/
The Forefront UAG blog: http://blogs.technet.com/b/edgeaccessblog/