1 Reply Latest reply on Mar 18, 2021 10:24 AM by Nico Heinze

    ROlling back after Delete | PowerCenter + MS SQL Server

    asagpariya_ciber Active Member

      Hi Team,


      A table has millions of records. Group of records can be identified through batch numbers. We have developed a mapping, which first identify batch number to be deleted from the table and then pass it to target (session has only delete)


      Issue with this job is, with few millions records, job runs fine but when we move same to production which has nearly 91 million records for a given BATCH number, it shows issue.


      It deletes records, but at the end of it rolls back. Log just shows [FATAL Error] nothing else. We made changes in commit interval as well, but of no use.


      I believe, that is because of 91+ million records and we are using only BATCH number to perform delete.


      What could be alternate way to do so?


      One way I was thinking to use TRANSACTION control transformation, where if BATCH ID change, it sends commit. But again I believe it will have same issue.


      Any other way around?

        • 1. Re: ROlling back after Delete | PowerCenter + MS SQL Server
          Nico Heinze Guru

          I fear the production DB needs more transaction log space for those batch DELETE's than it has at its disposal.

          Let me give an example:

          In one of my projects I have built a process in PowerCenter to export huge numbers of workflows from huge folders and to import them into another environment.

          The export was no problem at all.

          But while importing those huge XML files we received error messages from the repository DB that there was not enough transaction log space available to keep all those records for this huge transaction "alive".

          We had to ask the DBAs to change the transaction log size from 2 GB to 10 GB for this process. Then everything worked fine.


          Please ask your DBAs to check whether the problem is indeed the transaction log size (which can be set for each database on a SQL Server machine individually, but of course it has to fit into the existing disk space).

          If so, the remedy is to increase it. How much? I can't tell, that depends on the size of the records to be deleted.


          If that doesn't work for whatever reason, there's an alternative, but I would prefer to discuss this only if needed. No use in discussing hypothetical processes by now, I think.