Skip to main content
Kofax

Purge Stats tables in SQL Server per date

21057

QAID # 21057 Published

Question / Problem:

How can I purge Kofax Capture database stats tables in SQL Server, per date?

Answer / Solution:

To purge SQL Server Kofax Capture database stats tables per date, you can run the following query:

DECLARE @ThruDate DATE = DATEADD(DAY, -3, GETDATE())

BEGIN
DELETE FROM StatsBatch
WHERE StatsBatch.ExternalBatchID IN

   (SELECT ExternalBatchID FROM StatsBatch
   WHERE ExternalBatchID NOT IN

      (SELECT ExternalBatchID FROM StatsBatchModule
      WHERE EndDateTime IS NULL OR EndDateTime > @ThruDate));

DELETE FROM StatsBatchModule
WHERE StatsBatchModule.BatchModuleID IN

(SELECT BatchModuleID
   FROM StatsBatchModule
   WHERE EndDateTime <= @ThruDate);

DELETE FROM StatsFormType
WHERE StatsFormType.BatchModuleID IN

   (SELECT BatchModuleID FROM StatsFormType
   WHERE BatchModuleID NOT IN

      (SELECT BatchModuleID FROM StatsBatchModule
      WHERE EndDateTime IS NULL OR EndDateTime > @ThruDate));

DELETE FROM StatsModuleLaunch
WHERE StatsModuleLaunch.ModuleLaunchID IN

   (SELECT ModuleLaunchID
   FROM StatsModuleLaunch
   WHERE EndDateTime <= @ThruDate);

END;

IMPORTANT: Perform a backup of the database before using this.

Note: You can change DATEADD(DAY, -3, GETDATE()) number to the number of days that you want to delete.

Applies To:

Product Version
CAPTURE 10.0
  10.1
  10.2
  11.0
  • Was this article helpful?