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.
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.
5. Setup the SQL agent job to trigger at 00:25hrs every day.
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.
SELECT COUNT(*) AS CTMTradesCount from CTMTrades
SELECT COUNT(*) AS CTMMessagesCount from CTMMessages
SELECT COUNT(*) AS CTMBlockSplitsCount from CTMBlockSplits
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.
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.
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