000033219 - How to create a SQL Server Agent Job to Purge Rows from the tblDatafeedMessage table to reduce database size for the RSA Archer Instance database

Document created by RSA Customer Support Employee on Aug 15, 2016Last modified by RSA Customer Support Employee on May 31, 2017
Version 6Show Document
  • View in full screen mode

Article Content

Article Number000033219
Applies ToRSA Product Set: Archer
Platform: Windows SQL Server
Platform (Other): SQL Server (tested in 2008 R2 and newer)
 
IssueWhen 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:
  1. A large number of Data Feeds
  2. Frequently running Data Feeds (every 5 minutes)
  3. Data Feeds that update a significant volume of data each run. 
By default, Archer does not automatically purge old data from the tblDatafeedMessage table.  The table contains the Run Detail Messages from a Data Feed's Execution History.  Purging old rows from this table can help reduce the overall size of the database.
Sample image from ACR
User-added image
Resolution
  1. Open SQL Server Management Studio and expand the SQL Server Agent node in the Object Explorer pane:
                   User-added image
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
SET @count = 10000
 
    DELETE FROM tblDatafeedMessage
    WHERE datafeed_message_id IN (
      SELECT TOP (@count) datafeed_message_id
      FROM tblDataFeedMessage
      WHERE create_date < DATEDIFF(DAY,7,GETUTCDATE())
    )

User-added image

 
  1. Click OK to save the step
  2. Choose Schedules from the left pane
  3. Click the New… button
  4. Provide a Schedule name (ie DataFeedMessagePurge)
  5. Make sure the following settings are in place:
    1. Schedule type: Recurring
    2. Enabled=checked
    3. Frequency=Weekly 
    4. Recurs every=1 week
    5. Sunday is checked
    6. Occurs once at: 3:00 AM
    7. Start date={todays date}
    8. No end date selected
  6. Click Ok to save the schedule.
  7. Click Ok to save the job.
NotesYou 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

Attachments

    Outcomes