HOW TO FIX REPORTS IN SYSTEM CENTER The import-module below is needed to be run before any of the following can be in PowerShell. import-module “C:\Program Files\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1” Get-SCDWJob -ComputerName tarscsmdw02 | Sort-Object name | ft -autosize Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Transform.Common" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.Common" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "MPSyncJob" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "DWMaintenance" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterChangeAndActivityManagementCube" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterPowerManagementCube" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterWorkItemsCube" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterConfigItemCube" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterServiceCatalogCube" Disable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterSoftwareUpdateCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Transform.Common" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Load.Common" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterChangeAndActivityManagementCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterPowerManagementCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterWorkItemsCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterConfigItemCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterServiceCatalogCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterSoftwareUpdateCube" Disable-SCDWJob -computername TARSCSMDW02 -JobName "MPSyncJob" Disable-SCDWJob -computername TARSCSMDW02 -JobName "DWMaintenance" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Transform.Common" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Load.Common" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterChangeAndActivityManagementCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterPowerManagementCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterWorkItemsCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterConfigItemCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterServiceCatalogCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterSoftwareUpdateCube" Stop-SCDWJob -computername TARSCSMDW02 -JobName "MPSyncJob" Stop-SCDWJob -computername TARSCSMDW02 -JobName "DWMaintenance" Before restarting, make sure to run all Stop-SCDWJob and Disable-SCDWJob. TARSCDB01 TARSCDWDB01 TARSCSM03 TARSCSMDW02 Enable-SCDWJob -computername TARSCSMDW02 -JobName "DWMaintenance" Start-SCDWJob -computername TARSCSMDW02 -JobName "DWMaintenance" d:\scripts\dw.ps1 Enable-SCDWJob -computername TARSCSMDW02 -JobName "MPSyncJob" Start-SCDWJob -computername TARSCSMDW02 -JobName "MPSyncJob" d:\scripts\mp.ps1 Enable-SCDWJob -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Load.Common" Start-SCDWJob -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Start-SCDWJob -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Start-SCDWJob -computername TARSCSMDW02 -JobName "Load.Common" d:\scripts\SCDWJob.ps1 Enable-SCDWJob -computername TARSCSMDW02 -JobName "Transform.Common" Start-SCDWJob -computername TARSCSMDW02 -JobName "Transform.Common" d:\scripts\common.ps1 Enable-SCDWJob -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" Start-SCDWJob -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Start-SCDWJob -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" d:\scripts\scdwjob.ps1 Get-SCDWJob -ComputerName tarscsmdw02 | Sort-Object name | ft -autosize Once the jobs above have finished, run the next lines. Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "DWMaintenance" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "MPSyncJob" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.OMDWDataMart02" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.CMDWDataMart02" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Load.Common" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Transform.Common" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Extract_EIT RTM" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Extract_DW_SCSM03" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterChangeAndActivityManagementCube" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterPowerManagementCube" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterWorkItemsCube" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterConfigItemCube" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterServiceCatalogCube" Enable-SCDWJob -computername TARSCSMDW02 -JobName "Process.SystemCenterSoftwareUpdateCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterChangeAndActivityManagementCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterPowerManagementCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterWorkItemsCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterConfigItemCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterServiceCatalogCube" Enable-SCDWJobSchedule -computername TARSCSMDW02 -JobName "Process.SystemCenterSoftwareUpdateCube" Get-SCDWJob -computername TARSCSMDW02 -jobname "Extract_EIT RTM" -numberofbatches 3 Get-SCDWJobModule -computername TARSCSMDW02 -jobname mpsyncjob Go to this website and follow the instructions. You’ll want to start from line 1. Run Get-SCDWJob -computername TARSCSMDW02… about a third the way through the page. http://dougsigmon.wordpress.com/2012/09/20/dwmaintenance-stalled-processes-kill-the-scsm-2012-data-warehouse/ https://docs.microsoft.com/en-us/troubleshoot/system-center/scsm/troubleshoot-stuck-data-warehouse-jobs This web page is saved a PDF as well, SystemCenterReportsFixWebPage. These SQL scripts should be run on TARSCDWDB01 against DWStagingAndConfig02 database under TARSCDWDB01\SVCMGR. Run each line one at a time and take the output of each to put after the equal (=) sign. You should only need to run these if the jobs above do not run correctly. These SQL scripts give more details of issues per the instrucitons on the website at the top of this document. select processId from infra.process where processname like 'DWMaintenance' select batchid from infra.batch where processId = 1 select * from infra.WorkItem(nolock) where BatchId = 2169070 update infra.WorkItem set StatusId = 3 where BatchId = 2169002 update infra.WorkItem set RetryCount = 0 where BatchId = 2169002 update infra.WorkItem set ErrorCount = 0 where BatchId = 2169002 update infra.WorkItem set ErrorSummary = NULL where BatchId = 2169002 update infra.WorkItem set TakenTime = NULL where BatchId = 2169002 update infra.WorkItem set UpdateTime = NULL where BatchId = 2169002 update infra.WorkItem set CustomInfo = NULL where BatchId = 2169002 update infra.WorkItem set NotToBePickedBefore = NULL where BatchId = 2169002 select * from Infra.Batch WHERE BatchId = '2169086' UPDATE Infra.Batch SET BatchEndTime = '2021-05-27 03:55:16.293' where BatchId = '2169002' UPDATE Infra.WorkItem SET StatusId = 6 WHERE BatchId = '2169086' UPDATE Infra.Batch SET StatusId = 6 WHERE BatchId = '2169086' EXEC Infra.CreateBatch 'DWMaintenance' EXEC Infra.CreateBatch 'Load.Common' EXEC Infra.CreateBatch 'Load.OMDWDataMart02' EXEC Infra.CreateBatch 'Load.CMDWDataMart02' EXEC Infra.CreateBatch 'Transform.Common' EXEC Infra.CreateBatch 'Extract_DW_SCSM03' EXEC Infra.CreateBatch 'Extract_EIT RTM' EXEC Infra.CreateBatch 'MPSyncJob' SELECT * FROM LockDetails UPDATE infra.ProcessCategory SET IsEnabled = 1 WHERE IsEnabled = 0 EXEC dbo.ReleaseLock @ResourceName = 'Infra.ProcessCategory', @LockRequester = 'MPSync' Run the following scripts (located by clicking on the link) on TARSCDWDB01. Make sure no jobs are running before shrinking the logs. Backup the databases before shrinking the log file. Delete older backups. \\tarfs01\data\Users\Groups\COMPSERV\Knowledge Base\Documentation\SCOM Docs LogFileSize.sql – show the log file size of all databases DWDataMart02ShrinkLogFile.sql – shrinks the log file for this database to 100 MB DWRepository02ShrinkLogFile.sql – shrinks the log file for this database to 100 MB If the Extract ETL jobs are not starting at the scheduled time, restart the four servers after following all the steps for freeing the stuck cubes. Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterChangeAndActivityManagementCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00 Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterPowerManagementCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00 Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterWorkItemsCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00 Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterConfigItemCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00 Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterServiceCatalogCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00 Set-SCDWJobSchedule -computername TARSCSMDW02 -JobName Process.SystemCenterSoftwareUpdateCube -ScheduleType Weekly -WeeklyFrequency Monday, Tuesday, Wednesday, Thursday, Friday, Saturday, Sunday -WeeklyStart 03:00