OC Database Archiving Scheduler

Issue

How to configure SQL server agent job to auto-execute database archiving script ?

Solution

Below are the steps to configure SQL Server Agent :

Add the stored procedure [dbo].[OCAutoArchive] to the Optitrade Confirm Database.
Ensure service account is granted “execute” permission on stored procedure.
The [dbo].[OCAutoArchive] stored procedure needs to executed every day at at a set time, for example 00:25, to archive the trade tables within the OptitradeConfirm SQL database.
One of the ways of doing this is using the SQL Agent and scheduling a task for it to be executed at a set time every day. In most cases the DBA will take care of this task.
Setup the SQL agent job to trigger at 00:25hrs every day. ( Clients can configure the timing as per their preference )
The DBA can run the below queries against the OC database before and after running the SQL Agent Job to ensure archiving is taking place.

USE [OptitradeConfirm]
--live tables
SELECT COUNT(*) AS CTMTradesCount from CTMTrades
SELECT COUNT(*) AS CTMMessagesCount from CTMMessages
SELECT COUNT(*) AS CTMBlockSplitsCount from CTMBlockSplits
--archive tables
SELECT COUNT(*) AS CTMArchiveTradesCount from CTMArchiveTrades
SELECT COUNT(*) AS CTMArchiveMessagesCount from CTMArchiveMessages
SELECT COUNT(*) AS CTMArchiveBlockSplitsCount from CTMArchiveBlockSplits

Record the query output.
Next execute the SQL agent job.
Re-run the same queries again and check output.
After archiving, the amount of records in the live tables should reduce and the amount of records in the archive tables should increase.

Database Server:

The physical disk space on the database server must also be monitored regularly by DBA to ensure that there is enough space for the database MDF and log files to accommodate more data arriving in the system.

Each SQL Server database stores data in two files:

The Primary Database file, with extension .MDF
The Log file, with extension .LDF

The size and growth of these two files must be monitored to ensure the database does not run out of memory to process messaging data. This is the responsibility of DBA. They can set to automatically grow in order to accommodate more data.

Additionally, the data content should be shrunk periodically, and the database transaction logs truncated, to release unused space.

If you have further questions or need assistance in configuring DB archiving procedure, please contact Optitrade Tech Support via the Kofax Support Portal.

Level of Complexity

High

Applies to

Product	Version	Build	Environment	Hardware
Kofax Optitrade Confirm	OC 5.x+