Deleting an Automation Process That Contains Lots of Contacts in Kentico 8
Sometimes, you may need to delete an automation process, even though it contains many contacts. For example, when you realize that the process is wrong or not needed anymore as it was only a test.
It is possible to do this in Kentico 9 but not allowed in Kentico 8 versions—you would get an error message and need to delete all processed contacts first. This can be done easily if you only have a few contacts in the process, but if there are thousands of them, it would be very time consuming to click on each of them manually to delete them (there isn’t a “delete all” button). Additionally, if there were any contacts waiting in the “wait” steps, you would also need to delete the scheduled tasks generated by the process.
However, as always, there is a solution. You can use the following SQL script to delete all contacts (and their corresponding scheduled tasks) from a specific marketing automation process at once:
USE SomeDatabase –- Use a name of your database
DECLARE @AutomationCodeName NVARCHAR(MAX) = 'SomeAutomationProcess' –- Insert here a code name of the automation process
DECLARE @AutomationID INT = (SELECT WorkflowID FROM CMS_Workflow WHERE WorkflowName = @AutomationCodeName AND WorkflowType = 3)
DELETE FROM CMS_ScheduledTask WHERE TaskClass = 'CMS.Automation.AutomationTimer' AND TaskAssemblyName = 'CMS.Automation' AND CAST(REPLACE(TaskName, 'Timeout.Wait.','') AS uniqueidentifier) IN (SELECT StateGUID FROM CMS_AutomationState WHERE StateWorkflowID = @AutomationID)
DELETE FROM CMS_AutomationHistory WHERE HistoryWorkflowID = @AutomationID
DELETE FROM CMS_AutomationState WHERE StateWorkflowID = @AutomationID
All you need for the SQL script to work is the Code name of the automation process (the second line of the above script), which can be found in the Marketing automation application -> Processes tab -> select and edit the process -> General tab -> Code name property:
After you run the script, you should be able to delete the automation process in the UI.
Please note: Even though the SQL query should work as expected (and was tested for versions 8.0 – 8.2), please always make sure that you back up your database first!