If all else fails: Reinstall the Data Warehouse (System Center Service Manager.)
IMPORTANT: Take a backup of SQL DBs before you reinstall DW. In case there are special SQL Objects that haven’t been exported or saved.
Reinstalling the DW is pretty straightforward and done in the main SCSM console. This post is targeted at individuals who know what they are doing with SCSM and DW. Hence I am not going to go through how to remove and reinstall. I though maybe more interesting to know what happens under the Hood.
Before DW is functional, it needs to run a number of (#53) deployment jobs in SQL (only visible in SQL). This has to happen before the DW can be registered in SCSM. (I don’t know, it seems that the DW will regulate this itself, so that if you register the DW directly after install the sync jobs will be queued. I am doing this very granularly)
So, while these deployment jobs are running the DWMaintenance job is stuck in Waiting status. (consequently all other DW Jobs are also “stuck” or “queued”). Notice the DWMaintenance is not just Waiting but also has Errors in the ErrorSummary (this prevents sync jobs from running)
Once these deployment jobs are finished, DW should create a new Batch job for DWMaintenance which will be released from Waiting and should then run ok.
Step-by-step
Immediately after installation has completed, SQL will look like this:
Query:
select WI.BatchId, WI.StatusId, WI.ErrorSummary from infra.WorkItem(nolock) WI
join infra.Batch BAT on WI.BatchId = BAT.BatchId
join infra.Process PRO on BAT.ProcessId = PRO.ProcessId
where PRO.ProcessName = ‘DWMaintenance’
select * from DeploySequenceView where DeploymentStatusId != 6
select * from DeploySequenceStaging
Until all the jobs in DeploySequenceView disappear. Ca 1 ½ hours
SQL will then look like this:
With a new Batch job for DWMaintenance.
In Powershell we can now see the DW_EXTRACT, Transform and Load jobs. Ready for registration.
You can check the Deployment with: select * from DeployItemView
All Items should be completed.
The Extract, Load and Transform jobs all then run, even though the DW is not registered it is an initialization of the jobs. They don’t take long to run through.
Now we can try to register the DW.
After successful Registration the Extract Job will run. All jobs can now be seen in Powershell via the Warehouse Cmdlets.
Start (Resume) the MPSyncJob if you can’t wait.
The jobs run in the following order (sort of):
MPSyncJob/Disable Deployment Jobs
MPSyncJob/Synchronize ServiceManger MPs
MPSyncJob/Create ServiceManagerExtracts
Extract_DW_/*
MPSyncJob/Associate Imported MP Vertex
Wait for a long time until all these jobs finish……. probably overnight.
Life In The Cloud 