Skip to main content
Kofax

OC Database Archiving Scheduler

Article # 3036121 - Page views: 15

Issue

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

 

Solution

Below are the steps to configure SQL Server Agent :

  1. Add the stored procedure [dbo].[OCAutoArchive] to the Optitrade Confirm Database.
  2. Ensure service account is granted “execute” permission on stored procedure.
    DB-1.png
  3. 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.
  4. 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.
    DB-2.png
  5. Setup the SQL agent job to trigger at 00:25hrs every day.  ( Clients can configure the timing as per their preference )
    DB-3.png
    DB-4.png
  6. 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
  1. Record the query output.
  2. Next execute the SQL agent job.
  3. Re-run the same queries again and check output.
  4. 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

Db-7.PNG

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.

DB-5.png

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

DB-6.png

 

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+      

 

  • Was this article helpful?