Data warehouse jobs fail and event ID 33502 is logged
This article helps you fix a problem in which data Warehouse jobs fail in Microsoft System Center 2012 Service Manager and event ID 33502 is logged.
Original product version: Microsoft System Center 2012 Service Manager, System Center 2012 R2 Service Manager
Original KB number: 3137611
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: <Batch ID>
Module name: TransformEntityRelatesToEntityFact
Message: ErrorNumber="50000" Message="Unable to acquire applock - another instance of the module must already be running." Severity="18" State="1" ProcedureName="InitializeTransform" LineNumber="52" Task="(null)"
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. It 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 1
If you are currently in a failed state, use Resolution 1 to process the backlog and return the operation to a functioning status. The other two methods are ways to prevent the issue from reoccurring. To do it, wait for the status of all data warehouse jobs to be displayed as Not Started or Failed, and then follow these steps:
On the data warehouse server, stop the HealthService service at an elevated command prompt. To do it, 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.
Update the following SQL query to reflect the
ModuleName
value of the module in theTransform.Common
job that's failing. This example usesTransformEntityRelatesToEntityFact
.Note
The simplest way to see the
ModuleName
value for the module that's failing is to open the Service Manager console, select Data Warehouse, select Data Warehouse again, select Data Warehouse jobs, and then selectTransform.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 update infra.workitem set statusid = 6 where batchid = @mybatchid update infra.batch set statusid = 6 where batchid = @mybatchid exec infra.CreateBatch N'Transform.Common' drop table #MyTempTable drop table #MyTempTable2 drop table #MyTempTable3
If the script above displays the following message:
"Unable to acquire applock - another instance of the module must already be running"
execute the following query 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
Restart the HealthService service at an elevated command prompt. To do it, run the following command:
Net Start HealthService
Note
- You may have to run the SQL query several times until it finishes quickly.
- Don't log off when you are running the query.
- Run the query as quickly as possible, because new data that's captured in Service Manager will surge to the data warehouse when the service is restarted.
- If it takes you a long time to run the query several times, you may have another surge of data.
- Use Resolution 3 as the long-term solution.
Resolution 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 it, follow these steps:
- In SQL Server Management Studio, connect to the FIM database, expand SQL Server Agent, and then select Jobs.
- Right-click
FIM_ScheduleReportingIncrementalSynchronizationJob
, select Properties, and then select Schedules. - Change the Occurs every value for
FIM_UpdateReportingIncrementalSynchronizationJobSchedule_1
to 2 hours.
Resolution 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 aren'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.
More information
For more information, see How to Change the Default Timeout Period for Data Warehouse Transform Jobs.
Feedback
https://aka.ms/ContentUserFeedback.
Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see:Submit and view feedback for