Blog post Technical, Tridion

Ways to Clean Up Old Publish Transactions in Tridion

If you ever published anything in Tridion, you are probably aware of the fact that the Publish Transactions are the result of publishing activity. When you want to publish an item to, for example, US market, then Publish Transaction is created and executed. As the result of the publishing activity, the item is published to Target, or it failed for some reason.

If you are happy with the result, you move on with your work. What you are probably not aware of is that the Publish transaction is there to stay unless you delete it. Which most of the content editors do not tend to do. The problem that tends to happen is that your Tridion environment can have millions of old Transactions dating back few years in the past, slowing down your system, and blocking Publishers from working in the end.

So how do you fix it?

Manual cleanup

Some organizations tend to require editors to clean up their Transactions by hand. This reasoning is fine, and in the end, if you sent something to publish, you should verify that it is published as it was supposed to and you should remove your Transactions to clean up the system. However, as with any human-mandated activity, it is flawed because not all editors tend to follow the rules, so in most cases, Transactions do not get deleted.

Automatic Cleanup Activities

Another option is to have some automatic cleanup in place, a mechanism that will remove old Transactions on a regular basis. There are a couple of alternatives at hand for it, each one with its pros and cons.

Removing Transactions Using Core Service

It is the most common one and consists of creating a Core Service script that retrieves the Transactions matching specific criteria and removes them.

For example, the following code sample specifies how to retrieve the list of successful Transactions for a Target and Publication:

PublishTransactionsFilterData publishTransactionsFilter = new PublishTransactionsFilterData() 
{ 
    BaseColumns = ListBaseColumns.Extended, 
}; 
publishTransactionsFilter.TargetType = new LinkToTargetTypeData { IdRef = "tcm:0-4-65538" }; 
publishTransactionsFilter.PublishTransactionState = PublishTransactionState.Success; 
publishTransactionsFilter.ForRepository = new LinkToRepositoryData { IdRef = "tcm:0-268-1" }; 
XElement transactions = Client.GetSystemWideListXml(publishTransactionsFilter);

You can then filter the resulting xml to get the Ids of the Transactions that are older than 7 days:

List<string> transactionIDs = new List<string>(); 
transactionIDs.AddRange(from transaction in transactions.Elements() 
                                        where (DateTime.Now - DateTime.Parse(transaction.Attribute("StateChangeDate").Value)).TotalDays > 7 
                                        select transaction.Attribute("ID").Value);

After that you can just remove them one by one (or in batches if you prefer):

foreach (string transactionID in transactionIDs) 
{ 
    Client.Delete(transactionID); 
} 

The pros of this approach are:

  • Everything is done using API, so full support is guaranteed
  • it is highly customizable (meaning that criteria for retrieving Transactions can be fine-tuned to specific customer needs)
  • It can be scheduled and executed outside of working hours to have the least impact on the system
  • It can be split into more minor tasks by Publications/users/Targets/dates.
  • It can be executed from anywhere with access to the Core Service endpoint.

The cons are:

  • It can be resource-consuming activity especially if the number of Transactions is high
  • Deleting Transactions takes time, so it’s not the fastest solution.

Removing Transactions Using Event System

Transactions can also be removed using Event System. This alternative is acceptable if keeping the Transactions is irrelevant for your business case. This way Transaction can be deleted the moment it is finished. As a result, Editors will not see successful Transactions in the queue at all. They will be deleted the moment they are finished with the successful state.

The code would look something like this:

public ContentEventSystem() 
{ 
    EventSystem.Subscribe<PublishTransaction, SaveEventArgs>(PublishTransactionSave, EventPhases.TransactionCommitted); 
} 
private void PublishTransactionSave(PublishTransaction transaction, SaveEventArgs saveEventArgs, EventPhases eventPhase) 
{ 
    if (!saveEventArgs.IsNewItem) 
    { 
        PublishTransactionState state = transaction.State; 
        if (state == PublishTransactionState.Success) 
        { 
            transaction.Delete(); 
        } 
    } 
} 

The pros of this approach are:

  • Transaction queue is always clean and contains no successful Transactions.
  • Code is pretty straightforward to implement and maintain

The cons are:

  • Content Editors might find it difficult to adjust to disappearing Transactions.
  • It will bring overhead to all Publisher servers due to the fact that Event System is running for every Transaction update.
  • Failed Transactions are not covered with this approach; therefore, they will still exist in the system and there is no option to delete them afterwards using the Event System.

Removing Transactions Using PowerShell

According to the official documentation, Transactions can be removed using PowerShell script. The script can be scheduled as a regular task using Task Scheduler. Following script sample removes all Transactions and writes their data to the log file.

if (Get-Module -ListAvailable -Name Tridion.ContentManager.Automation) 
{
   Import-Module -Name "C:\Program Files (x86)\SDL\Tridion Sites\bin\PowerShellModules\Tridion.ContentManager.Automation\Tridion.ContentManager.Automation.dll" -Verbose
   Write-Host "Module Tridion-ContentManager-Automation" 
}
else 
{
   Write-Host "Module Tridion-ContentManager-Automation not installed, so skipped loading"
}
$daysToKeepSuccess = 7
$daysToKeepAll = 14
$transactions = Remove-TcmPublishTransactions -Successful -Before (Get-Date).AddDays(-$daysToKeepSuccess) 
$transactions += Remove-TcmPublishTransactions -Before (Get-Date).AddDays(-$daysToKeepAll)

foreach($transaction in $transactions)
{
  $transactionID = $transaction.Id
  $Name = $transaction.Title
  $ItemId = $transaction.Items.IdRef
  $target = $transaction.ListInfo.PublicationTargetTitle
  $publication = $transaction.ListInfo.PublicationTitle
  $path = $transaction.ListInfo.ItemPath
  $action= $transaction.ListInfo.PublishAction
  $state =$transaction.State
  $Priority = $transaction.Priority
  $time = $transaction.StateChangeDateTime
  $User = $transaction.Creator.Title

  "$transactionID,$Name,$ItemId,$target,$publication,$path,$action,$state,$Priority,$time,$User" | Out-File ($pwd).path + "\Log.txt" -NoClobber -Append -force
}

The pros of this approach are:

  • Script is really easy to implement and consists of a single command with few parameters.
  • it is partially customizable (meaning that criteria for deleting transactions can be based on dates or their state, but nothing other than that.)
  • It can be scheduled and executed outside of working hours to have the least impact on the system
  • It can be split in more minor tasks by dates only (delete first oldest Transactions, then newer).
  • It’s fast.

The cons are:

  • Partial customization is sometimes just not enough. For example, the script cannot delete only Transactions in the Warning state.
  • It must be executed from the CM server (or remote).

The forbidden way

There is another option that we should mention, but it should not be used. It means that Transactions can be removed from the database directly, executing SQL database queries. However, modifying the database using SQL queries, outside of official APIs and direct scripts provided by the RWS, results in loss of support for the product, so it should not be taken lightly.

I, myself would prefer using the Core Service. It is highly customizable, and gives you all the options you need. For me, second place takes the PowerShell script, due to the simplicity and ease of use it brings to the table. Other options I would not consider unless in some rare edge cases.

If you have any questions, feel free to contact us.

Contact us to discuss your project.
We're ready to work with you.
Let's talk