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:

Log Name: Operations Manager
Source: Data Warehouse
Event ID: 33502
Level: Error
Description:
ETL Module Execution failed:
ETL process type: Transform
Batch ID: ######
Module name: TransformEntityRelatesToEntityFact
Message: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.


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 believe that this is a short-term and isolated problem, process the backlogged transformed jobs to return the operation to a functioning status. 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. 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: 03/28/2016 17:52:00 - Revision: 3.0

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

  • kbexpertiseadvanced kbsurveynew kbtshoot KB3137611
Feedback