none
Transform.common job failing on module TransformEntityRelatesToEntityFact - Unable to acquire applock - another instance of the module must already be running

    Question

  • Hi, I’m 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). It’s 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 (there’s 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 there’s a TransformEntityRelatesToEntityFact module already running which is causing the current run of the job to fail. I’ve restarted the services, I’ve disabled all DW jobs and only ran tansform.common, I’ve 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 I’ve 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
    Thursday, August 16, 2012 8:59 AM

Answers

  • 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
    Wednesday, August 22, 2012 9:16 AM

All replies

  • Please open support case.

    SCSMSolutions
    email: freemanru (at) gmail (dot) com

    • Proposed as answer by CCOTS-KN Monday, August 18, 2014 4:19 PM
    Thursday, August 16, 2012 8:48 PM
    Moderator
  • 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
    Wednesday, August 22, 2012 9:16 AM
  • Your solution works for me as well but this keeps happening.  Have you determined a root cause?

    Friday, May 3, 2013 2:50 PM
  • 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
    Tuesday, July 2, 2013 2:27 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)

    Monday, November 4, 2013 3:20 AM
  • 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.

    Tuesday, November 5, 2013 2:42 AM
  • 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.
    Wednesday, November 6, 2013 5:56 AM
  • 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
    Tuesday, November 26, 2013 12:55 PM
  • did you ever get a fix around this problem
    Sunday, November 16, 2014 11:24 PM
  • Hello,

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

    Regards,

    Monday, March 2, 2015 2:51 PM
  • Make sure you apply the latest update rollup
    • Proposed as answer by Slovyanin Tuesday, July 31, 2018 7:47 AM
    Thursday, April 16, 2015 2:19 PM
  • I've applied the latest update rollup but still have this problem, but I only have it at nights at some specific time - every night. During the hole day I don't have such problem. Could somebody please explain me what happens at night at this specific time with my TransformEntityRelatesToEntityFact.
     I've checked watermarks - all correct
    • Edited by abdulax Wednesday, May 31, 2017 7:43 AM
    Wednesday, May 31, 2017 7:37 AM
  • This solution always seems to do the trick: https://social.technet.microsoft.com/wiki/contents/articles/37780.scsm-datawarehouse-jobs-failing.aspx?wa=wsignin1.0

    I've only had to advance the watermark once, and that was after identifying problem record so I could add it back in the event of data loss.

    • Proposed as answer by Jason Decou Wednesday, October 4, 2017 2:42 PM
    Wednesday, October 4, 2017 2:40 PM