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

  • Article

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.