Skip to main content
Kofax

OC Database Archiving Scheduler

DB Archiving is crucial for the stability and performance of the application. 

The scheduler should be configured to execute overnight and is monitored/maintained by the Client.

Question / Problem :

How to configure schedulers to execute database archiving ?

Answer / 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.

clipboard_ed9137b043bf3f8546d583ce9b344336c.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.

clipboard_e0c72b3f0ff03b38a5896743fe2fef364.png

5.  Setup the SQL agent job to trigger at 00:25hrs every day.

clipboard_e07660443aa6b294080a5dff1aa24e5be.png

 

clipboard_e9a6c05c76efc8a3d43a84723d6685588.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


7. Record the query output.

8. Next execute the SQL agent job.

9. Re-run the same queries again and check output.

10. 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

clipboard_e44269cddbdd252d33077a2f82492d326.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.

clipboard_e65423c52ff3abf1f31758e857a4538d0.png

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

clipboard_e6f76983d029244865755f788a3ae9f92.png

 

Hope this article is helpful.

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

 

AUTHOR : Anu Soundararajan

 

 

  • Was this article helpful?