Purge Stats tables in SQL Server per date

Issue

Purge Kofax Capture database stats tables in SQL Server, per date.

Solution

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

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

DECLARE @ThruDate DATE  
SET @ThruDate = '2014-12-31';

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;

Note: You can change ThruDate to be the date you want to delete thru.

Level of Complexity 

Moderate

 

Applies to  

Product	Version	Build	Environment	Hardware
Kofax Capture	11.x	N/A	N/A	N/A

References

21057

 

Article # 3039268