SCSM DW jobs query for stalled processes.

April 3, 2013 at 7:40 am in Service Manager by kurtvh

Sometimes you can encounter challenges to keep the ETL data warehouse and cube jobs processing. The procedure to investigate is nicely explained in this blog. For readability of this blog an overview of the symptoms:

  • All Data Warehouse jobs disabled.
  • DWMaintenance job never finishes or runs a really long time.
  • Reports don’t show updated data.
  • Failed cube processing
  • Processing is just stalled
  • DWMaintenance is stalled on the ManageCubePartitions/ManageCubeTranslations Step
  • The data warehouse is just dead.

In some cases the root cause of these issues is DWMaintenance job that has a stalled process. It’s a statusID of 7 (waiting) instead of 3 (not started). You need to reset this status to get the Data Warehouse jobs running again. I also have run through this procedure to see if there are no stalled processed on other jobs and get some error details on hanging processes (if there are). Going through this procedure for every job was a bit time consuming and therefore I have created a query that executes this procedure and returns jobs with statusID 7.

Run this query on your SQL server against the against the DWStagingAndConfig database. Select the database and click New Query. Specify the Extract_ job names in the query and execute the query.

select
DWProcess.ProcessName,
DWWorkItem.StatusId,
DWWorkItem.WorkItemId,
DWWorkItem.ErrorCount,
DWWorkItem.ErrorSummary,
DWWorkItem.RetryCount,
DWWorkItem.UpdateTime

from
Infra.Process AS DWProcess

LEFT OUTER JOIN
Infra.Batch AS DWBatch ON
DWBatch.ProcessId = DWProcess.ProcessId

LEFT OUTER JOIN
Infra.WorkItem(nolock) AS DWWorkItem ON
DWBatch.BatchId = DWWorkItem.BatchId

where DWProcess.ProcessName IN (‘<Extract_DW_Your DW mgmt group name>‘,’<Extract_mgmt group name>
    ,’DWMaintenance’,’MPSyncJob’, ‘Load.Common’, ‘Transform.Common’,’Load.OMDWDataMart’,’Load.CMDWDataMart’)AND
    DWWorkItem.StatusId = 7

This will return all jobs with the StatusID on 7…. if there are.

Fixing the issue is the same as in the manual procedure: (Check the PowerShell script form Travis (here) that will run each of the ETL (Extract-SM, Extract-DW, Transform, Load) jobs for the data warehouse in sequence and then starts the Deployment and Cube Processing Jobs.)

  • First you need to lad the Datawarehouse cmdlets – Use one of these depending on your install path

import-module “C:\Program Files\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets”

or

Import-Module ‘%ProgramFiles%\Microsoft System Center 2012\Service Manager\Microsoft.EnterpriseManagement.Warehouse.Cmdlets.psd1′

  • Run Get-SCDWJob and top it with Stop-SCDWJob –Jobname DWMaintenance. Do the same for any other running job.
  • Execute the SQL query

update infra.workitem set statusid = 3 where workitemId = <output SQL query>

  • Run the start-SCDWJob -jobname MPSyncJob PS command to get the jobs started.

Hope this helps!

Kurt Van Hoecke

Digg thisTweet about this on TwitterShare on LinkedInShare on Facebook