Hi, Im having issues with the Transform.Common job failing in my SCSM 2012. This did run ok until early July and has failed since.

When I run a get-scdwjob it shows the job start date of 10/7/2012 which is last month and a finish date of 16/08/2012 (today). Its almost like the job has never completed even though it fails every run. Also the BatchID of the transform.common job has not changed in quite some time.

The module which is failing is 1306 TransformEntityRelatesToEntityFact, if I run a get-scdwjobmodule and pipe the output to a file this is part of what I get (theres pages showing every failed run)-

"</Error><Error EventTime="2012-08-15T16:32:28.5406785Z">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)"</Error></Errors>

ModuleTypeName      : System

ModuleName          : TransformEntityRelatesToEntityFact

ModuleDescription   : Deployment Execution Step

JobName             : Transform.Common

CategoryName        : Transform

Description         : Transform.Common

CreationTime        : 15/08/2012 16:32:28

The same error from Operations Manager Event Log-

ETL Module Execution failed:

ETL process type: Transform

Batch ID: 5954

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)"

So, it seems the transform.common job thinks theres a TransformEntityRelatesToEntityFact module already running which is causing the current run of the job to fail. Ive restarted the services, Ive disabled all DW jobs and only ran tansform.common, Ive extended the period in-between runs of transform.common and I still get this error every time it runs. The job does create a temporary table in the DWRepository DB which Ive also tried dropping then running the task and it still fails.

Any ideas?

Cheers,

Max.

• Edited by MaxUK Thursday, August 16, 2012 10:27 AM

Please open support case.

This is now fixed, I ran the get-scdwwatermark command against the failing module to find which other modules, etc were attached to it. One of these showed a date watermark of mid July - the same date and time which showed as the job start time when I ran a get-scdwjob. I reset this to a day later using set-scdwwatermark and started the job running which succeeded ok. So, transform.common is now working and we can run reports against the once missing data. I can only assume that something funny must have happened to the mid July run which caused the job to fail but not quite end, resetting the watermark fixed this.

Cheers,

Max.

• Marked as answer by MaxUK Wednesday, August 22, 2012 9:16 AM

Your solution works for me as well but this keeps happening.  Have you determined a root cause?

I have been told that your fix will cause DW to lose all data from the time when the job failed until the new watermark time.

• Proposed as answer by CCOTS-KN Monday, August 18, 2014 4:19 PM

Hi MaxUK,

Thanks so much for this post. I am having the exact same issue but just cant seem to get a break. I have tried this but still does not work. can you please help? Many thanks in advance.

Hi maxUK,

I am having this persistent problem where i am trying to resume this one job and it just keeps getting failed.It was working fine for the first 1 month of the SCSM setup and it just stopped by itself with the following error. i have tried restarting the services on the DW server, tried restarting the DW server, tried manually resetting the watermark to a day later and ran it again but still failed. tried to disable and reenable this tranform.common module job which gets stuck at TransformEntityRelatesToEntityFact ( comes as failed and never seems to have resumed after 13th June 2013. all the other cubes and DW ETL process runs fine. This is the only exception which is stucking my report generation. Tried a lot of things from the forums on technet but still no luck. Event Error id 33502

keeps failing on this specific one. can somebody please help as i am new to SCSM and this is causing a bit of problem now with the stale reports. Many Many thanks in advance.

Thanks

Mic

ETL Module Execution failed:
 ETL process type: Transform
 Batch ID: 6061
 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)"

 Stack:    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
   at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader()
   at Microsoft.SystemCenter.Warehouse.Utility.SqlHelper.ExecuteReader(SqlConnection sqlCon, CommandType cmdType, String cmdText, SqlParameter[] parameters)
   at Microsoft.SystemCenter.Warehouse.Etl.StoredProcedure.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
   at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser, Int32 batchSize)
   at Microsoft.SystemCenter.Warehouse.Etl.TransformModule.Execute(IXPathNavigable config, Watermark wm, DomainUser sourceConnectionUser, DomainUser destinationConnectionUser)
   at Microsoft.SystemCenter.Etl.ETLModule.OnDataItem(DataItemBase dataItem, DataItemAcknowledgementCallback acknowledgedCallback, Object acknowledgedState, DataItemProcessingCompleteCallback completionCallback, Object completionState)

You should not use Max's solution as there is data loss.  I had to open a case with MS and found out there was an issue with our database.

Thanks so much. Unfortunately we do not  have any Support with MS which i am working on with my boss. But is it possible for me to know what was actually wrong and how did it get resolved. just a few lines if possible please. hopefullly it might give me some where to go beyond this dead end :) many  many thanks in advance.

I have read some old threads which point out that(Transform.common job failure) this was logged as a bug previously and was supposed to be fixed in SCSM 2012 SP1. I`m running SP1 and have the same issue.

The two failed modules are ID 1949 and 1947: TransformEntityRelatesToEntityFact and TransformEntityManagedTypeFact

These are the old watermarks for the failed entities.
get-scdwwatermark -computername ******* -ModuleType Transform -ModuleName TransformEntityRelatesToEntityFact

EntityName                 WaterMarkType              WaterMarkValue
----------                 -------------              --------------
EntityDim                  BatchId                    9532
RelationshipTypeDim        BatchId                    9532
DataSourceDim              BatchId                    9532
Relationship               DateTime                   12/11/2013 07:10:00

get-scdwwatermark -computername ******* -ModuleType Transform -ModuleName TransformEntityManagedTypeFact

EntityName                 WaterMarkType              WaterMarkValue
----------                 -------------              --------------
ManagedTypeDim             BatchId                    9532
EntityDim                  BatchId                    9532
DataSourceDim              BatchId                    9532
BaseManagedEntity          DateTime                   14/11/2013 17:09:57
TypedManagedEntity         DateTime                   14/11/2013 17:09:57


I have altered the watermarks with 1 day(see below) as proposed and rerun the data warehouse jobs. The same two modules failed again.



get-scdwwatermark -computername ******* -ModuleType Transform -ModuleName TransformEntityRelatesToEntityFact

EntityName                 WaterMarkType              WaterMarkValue
----------                 -------------              --------------
EntityDim                  BatchId                    9532
RelationshipTypeDim        BatchId                    9532
DataSourceDim              BatchId                    9532
Relationship               DateTime                   15/11/2013 07:10:00

get-scdwwatermark -computername ******* -ModuleType Transform -ModuleName TransformEntityManagedTypeFact

EntityName                 WaterMarkType              WaterMarkValue
----------                 -------------              --------------
ManagedTypeDim             BatchId                    9532
EntityDim                  BatchId                    9532
DataSourceDim              BatchId                    9532
BaseManagedEntity          DateTime                   15/11/2013 07:10:00
TypedManagedEntity         DateTime                   15/11/2013 07:10:00

did you ever get a fix around this problem

Hello,

Has anyone got a fix for this issue. I am now having the same problem with (TransformServiceContainsConfigItemFact).

Regards,