Data Warehouse jobs fail and event ID 33502 is logged

Symptom

Data Warehouse jobs fail in Microsoft System Center 2012 Service Manager. When this problem occurs, the following event is logged in the Operations Manager event log on the Data Warehouse server:


Also, when you run certain Data Warehouse-related cmdlets, you frequently see a time-out error recorded for the TransformEntityRelatesToEntityFact module that resembles the following:

Get-SCDWJobModule -JobName transform.common
. . .
1952 TransformEntityRelatesToEntityFact Failed
. . .

Cause

This problem can occur if the volume of transform data exceeds the amount that can be processed by the transform modules within the time-out period. This typically occurs after Data Warehouse jobs were disabled for some time because the volume of data to be transformed can become quickly backlogged. By default, Data Warehouse transform jobs have a hardcoded 60-minute time-out. 

Resolution

To fix this problem, use one of the following methods.

Method 1

If you are currently in a failed state use Method1 to process the backlogged and return the operation to a functioning status. . The other two methods are ways to prevent the issue from re-occurring. To do this, wait for the status of all Data Warehouse jobs to be displayed as Not Started or Failed, and then follow these steps:

  1. On the Date Warehouse server, stop the HealthService service at an elevated command prompt. To do this, run the following command:

    Net Stop HealthService 

    Note Depending on your version of Service Manager, this service name might be displayed as either Microsoft Monitoring agent or System Center Management.
  2. Update the following SQL Server query to reflect the ModuleName value of the module in the Transform.Common job that's failing. This example uses TransformEntityRelatesToEntityFact.

    Note The simplest way to see the ModuleName value for the module that's failing is to open the Service Manager console, click Data Warehouse, click Data Warehouse again, click Data Warehouse jobs, and then click Transform.Common. In the bottom-center pane, you can see a list of modules and the current status. After you make the changes, run the query.

    Use DWStagingAndConfig  
    declare
    @mybatchid INT,
    @mysourceid INT,
    @outXML XML,
    @myProcessCategoryName NVARCHAR(100),
    @myProcessName NVARCHAR(100),
    @myModuleName NVARCHAR(100),
    @sqlString NVARCHAR(150),
    @paramDef NVARCHAR(100)
    set @myProcessCategoryName = N'Transform'
    set @myProcessName = N'Transform.Common'
    set @myModuleName = N'TransformEntityRelatesToEntityFact'
    USE DWStagingAndConfig
    create table #MyTempTable (
    ProcessCategoryName NVARCHAR(150),
    ProcessName NVARCHAR(150),
    BatchId INT,
    BatchStatus NVARCHAR(150),
    WorkItemStatus NVARCHAR(150),
    WorkItems INT
    )
    insert #MyTempTable
    exec Infra.GetBatchDetails @ProcessCategoryName=@myProcessCategoryName, @ProcessName=@myProcessName
    select @mybatchid = BatchId from #MyTempTable
    select @mysourceid = sourceid from etl.source where SourceName='SCDW'
    create table #MyTempTable2 (
    myWaterMark XML
    )
    insert #MyTempTable2
    exec etl.GetWaterMark @BatchId=@mybatchid, @ModuleName=@myModuleName, @ProcessName=@myProcessCategoryName, @SourceId=@mysourceid
    select @outXML = myWaterMark from #MyTempTable2
    create table #MyTempTable3 (
    myWaterMark XML,
    BatchId INT,
    UpdatedRowCount INT,
    InsertedRowCount INT
    )
    USE DWRepository
    set @paramDef = N'@ioutXML XML'
    set @sqlString = 'insert #MyTempTable3 exec ' + @myModuleName + 'Proc @WaterMark=@ioutXML'
    exec sp_executesql @sqlString, @paramDef, @ioutXML=@outXML
    select @mybatchid = BatchId, @outXML = myWaterMark from #MyTempTable3
    USE DWStagingAndConfig
    exec etl.SetWaterMark @BatchId=@mybatchid, @ModuleName=@myModuleName, @ProcessName=@myProcessCategoryName, @SourceId=@mysourceid, @WaterMark=@outXML
    drop table #MyTempTable
    drop table #MyTempTable2
    drop table #MyTempTable3
  3. If the script above displays the following:
    "Unable to acquire applock - another instance of the module must already be running"
    execute the following from SQL Server Management Studio then re-execute the SQL Query in step 2:
    insert #MyTempTable
    exec Infra.GetBatchDetails @ProcessCategoryName='Transform', @ProcessName='Transform.Common'
    GO
    Update infra.workitem set statusid = 6 where batchid IN (SELECT myBatchID FROM #MyTempTable)
    GO
    Update infra.batch set statusid = 6 where processid = (SELECT ProcessId from infra.process where processname = 'Transform.Common')
    GO
    EXEC infra.CreateBatch 'Transform.Common'
    DROP TABLE #MyTempTable
  4. Restart the HealthService service at an elevated command prompt. To do this, run the following command:

    Net Start HealthService 


Note You may have to repeat these steps several times or in several modules.

Method 2

If you're using Forefront Identify Manager (FIM), this problem may recur because of the flow of data that reaches Service Manager. To spread the workload for this data, change the FIM_ScheduleReportingIncrementalSynchronizationJob schedule from the default value of every 8 hours to every 2 hours. To do this, follow these steps:

  1. In SQL Server Management Studio, connect to the FIM database, expand SQL Server Agent, and then click Jobs.
  2. Right-click FIM_ScheduleReportingIncrementalSynchronizationJob, click Properties, and then click Schedules.
  3. Change the Occurs every value for FIM_UpdateReportingIncrementalSynchronizationJobSchedule_1 to 2 hours.

Method 3

For a more long-term solution, upgrade to Microsoft System Center 2012 R2 Service Manager Update Rollup 4 (UR4) or a later version. Beginning in Update Rollup 4, Service Manager has an adjustable time-out setting. Also, the default Data Warehouse transform job time-out changes from 60 minutes to 180 minutes. If three hours isn't long enough for the Transform.Common module to finish, you can increase the value by changing the following registry value:

HKLM\SOFTWARE\Microsoft\System Center\2010\Common\DAL
SqlCommandTimeout = (DWord 32 bit in second)

Note If you're using Forefront Identity Manager, you must upgrade to Microsoft Identity Manager 2012 R2 to obtain support for Service Manager 2012 R2.
Properties

Article ID: 3137611 - Last Review: Mar 28, 2016 - Revision: 1

Microsoft System Center 2012 Service Manager Service Pack 1, Microsoft System Center 2012 R2 Service Manager

Feedback