Troubleshooting the Data Warehouse: Data Warehouse isn’t getting new data or jobs seem to run forever

This is the latest in “Troubleshooting the Data Warehouse” series. The goal of this series is to not only help you troubleshoot specific issues you may be experiencing but also teach you more about the data warehouse. For a complete list of posts in the series, check out Troubleshooting the Data Warehouse: An overview.


The symptom


I don’t see data or new data in my reports or ETL Jobs look like running forever and never in Not Started status.


The troubleshooting steps


To troubleshoot the above issue, please verify things in the following orders

1. Use the Get-SCDWJob PowerShell cmdlet to determine if all ETL jobs are enabled

Start PowerShell and type Get-SCDWJob

·         If the ETL jobs are disabled and either MPSyncJob or DWMaintenance job is running, you’ll need to wait a little bit to get the job status again (because these two background jobs disable the ETL jobs).  However, if these two jobs are Not Started and the ETL jobs are disabled, you can use the Enable-SCDWJob cmdlet to enable each of them. For example

Enable-SCDWJob -JobName Transform.Common


·         If ETL jobs, MPSync job and DWMaintenance job are all enabled and running, but their individual batch id has not changed for a long time or use Get-SCDWJobModule for specific job, we don’t see any module actually is running, check the event log and see if there is any error message. Sometimes, the error message might be many days old, we need to dig deep in the old day events.


·         Check if three services (System Center Data Access Service, System Center Management, System Center management Configuration) in Data warehouse Management server are actually running.

In Data Warehouse Management Server, click Start > Run .., enter Services.msc, in the Services window, verify if the following services are running or not

o   System Center Data Access Service

o   System Center Management

o   System Center management Configuration

If any of them are not running, restart all three services. In addition, if all services are actually running, events from Event Source Data Warehouse and OpsMgr SDK Service are outputted to Operations Manager event log. We can use this information as another source to verify if all services are running or not. If you don’t see events from Event Source Data Warehouse and OpsMgr SDK Service for a while, restart all three services regardless.


Comments (1)

  1. Andreas says:

    I know this is a rather old blogpost, but I can't find any information on how to restart the DW jobs after they have failed.

    The problem is exactly as you describe, the DW isn't getting any new data and when running the get-scdwjob the transform.common and load.common is in status = failed.

    I found the reason for the failure by running get-scdwjobmodule -jobname transform.common -moduleid 7628 | fl (and the reason was that the log-disk were full). That problem is now fixed, but as stated; We can't manage to restart the both failed jobs. Any help would be highly appriciated!

Skip to main content