Issues with Service Manager DW are very common and hard to troubleshoot because you do not want to break the DW where you keep all your data. There is a knowledge base article from Microsoft released a day ago to help you with the issue described in it. You can find it here.
Lately it is troubleshooting period for me. My latest issue was with Service Manager and DW. After unexpected restart of the Service Manager Management Server, Service Manager Data Warehouse server and the SQL server that was holding the databases MPSyncJob couldn’t run fully and one particular MP was stuck in Pending Association. Microsoft.SystemCenter.Datawarehouse.OLAP.Base was associated successfully in another 3 batch ID’s but the last batch ID was always on Pending Association.
In the Operations Manager event log on the Data Warehouse server you will also see error 33527 appearing constantly.
So how do I solved this issue? There is a solution unfortunately it has some downsides. You can follow these steps:
1. Unregister the Service Manager DW from the Service Manager console.
2. Wait at least one day. I’ve waited two days.
4. Logon to the Analysis Services instance for the Service Manager and delete any existing cubes.
5. Register the Service Manager DW again.
6. Wait one day to see if all DW jobs are finishing successful. Check if Reporting pane is appearing in the console and it is working normally.
7. Check the Operations Manager event log for errors on the DW server.
After these steps everything should be ok with DW jobs. Keep in mind that in your case this solution might not work.
Software I’ve used:
- Windows Server 2012
- SCSM 2012 SP1 UR2
- SQL 2012 SP1