Hi,
May I ask what are the official recommendations on how to keep the growth of an existing production MSSQL offload database in check?
In addition to the information which we already provide in the docs (architecture, configuration), the user would like to learn more about the recommended maintenance procedure and the requirements [1].
Thanks in advance,
[1] Extracted from the original request: “Do you provide any recommended maintenance advice on keeping growth in check? With the offload process running continuously there is conflict between the purge jobs that have been setup on the offload tables. There doesn’t appear to be a consistent way of portioning said tables so the code is attempting to delete records instead based (in some cases with the necessity to join back to another table to obtain a datestamp to use for ageing a record and determining it as a candidate for deletion). It just seems like the offload architecture we have is suffering at this scale, I wasn’t around when it was built so I’m trying to piece together information from various sources.”
The maintenance of the MSSQL central database is indeed not managed by us but should be done by the owner of the MSSQL database
As a tip to avoid the growth of the table i would implement a very similar way of cleaning up the tables as we do with our local MySQL databases
-delete the trending older than x time
-delete the alarms older than x time, unfortunately there is indeed a link with the properties table which doesn’t include a timestamp
In our case we first get the list of Alarm IDs that we will delete (older than x time) and delete the linked properties first.
-if you also have the info table, i would recommend to have this one also cleaned after x time, or don’t offload this one at all since these are mainly activities and updates being done in the system for debugging purposes
More details about the structure of the data: