|Applies To||RSA Product Set: Archer|
Platform: Windows SQL Server
Platform (Other): SQL Server (tested in 2008 R2 and newer)
|Issue||When running the Archer Configuration Reports (ACR) from the Archer Control Panel (ACP), the tblDatafeedMessage database table can be quite large and be among the top 10 for row count or size. |
This can happen when one or more of the following conditions is true:
Sample image from ACR
2. Right-click on Jobs and select New Job
3. A popup will appear. Enter in a name for the job (ie DataFeedMessagePurge )
4. Make sure the “Enabled” checkbox is selected and click the Steps page in the left pane
5. Click the New… button at the bottom of the page to create a new step for the job
6. Provide a step name (ie DataFeedMessagePurge )
7. Leave “Type” and “Run as” as the default and choose your Archer instance database from the database dropdown
8. Paste the following statement into the Command window: *** See notes at end of article to adjust number of days to retain ***
DECLARE @count int
|Notes||You can choose to run this job to run as frequently as you would like, however the table won’t grow to massive amounts very quickly, especially if you don’t have a large number of Data Feeds running in this environment. You can also alter the number of days it will go back and clear old messages. In the example above, it's currently set to remove anything older than 7 days in the script. |
Typically the only data in this table that may be important is for the last run data for each Data Feed. This is where the Data Feed "Run Detail' data in the front end is pulled from. Check the Data Feed schedules in your instance and configure this job to only purge data older than the value of your most infrequently scheduled Data Feed. For example if your most infrequently scheduled Data Feed runs every two weeks then configure the job to only purge data older than two weeks. In the example above, this would look like:
WHERE create_date < DATEDIFF(DAY,14,GETUTCDATE())
As written, this job only cleans up entries in groups of ten thousand at a time. In cases where there are large volumes of rows to delete, this process will take a long time to clean up entries. You may consider increasing the 10,000 value to larger volumes or completely truncate the table before implementing and scheduling this this job to run on a regular basis.
An enhancement request has been created as ARCHER-22333: Add options to purge Data Feed History much like the Retention Policy options for History Log fields.
Open an Archer Support Case to have your company added to the request.
Join an RSA Archer Working Group & See Upcoming Schedule: https://community.rsa.com/docs/DOC-32437