You are currently offline, waiting for your internet to reconnect

Service Manager 2010 Reports fail to include current data

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)
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
Before troubleshooting, you will first need to understand what a Batch, Module, and Workitem are.

 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

Select * 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 Deployment 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. 

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.
Note This is a "FAST PUBLISH" article created directly from within the Microsoft support organization. The information contained herein is provided as-is in response to emerging issues. As a result of the speed in making it available, the materials may include typographical errors and may be revised at any time without notice. See Terms of Use for other considerations.

Article ID: 2703027 - Last Review: 05/07/2012 19:32:00 - Revision: 1.0

  • Microsoft System Center Service Manager 2010
  • KB2703027