MicroStrategy - Schedule Administrative Tasks

mstr

https://community.microstrategy.com/t5/Server/TN19718-How-to-create-an-event-based-schedule-in-the/ta-p/179915

How can we invalidate/delete caches and History List messages in MicroStrategy Intelligence Server using a scheduled administration task?

Cache management can be automated in MicroStrategy Intelligence Server using the administration scheduling in MicroStrategy Desktop. In order to schedule administration tasks, follow the steps below:

  1. Login to MicroStrategy Desktop as an administrator and connect to a 3-tier project source.
  2. Go to the Administration menu and select Scheduling -> Schedule Administration Tasks. The Schedule Administration Tasks window opens.
  3. History List messages can either be deleted for all the projects or for selected projects only. This can be selected from the Available projects drop-down list.
  4. Select 'Delete History List messages' from the actions drop-down list
  5. Select one of the pre-defined schedules. The schedules used for the scheduling of administration tasks are the same schedules used for scheduling of reports and created under the Schedule Manager.

Several administration tasks can be performed using the 'Schedule Administration Tasks' window including invalidating/deleting caches as well as deleting History List messages.

History List messages can be deleted using several different criteria:

  1. Lifetime (days): Users can choose to delete messages that are older than a given number of days since creation. For example, if the number entered here is 10, the system will delete all the messages that are more than 10 days old and keep those that are less than 10 days old. A value of 0, which is the default value, means that all messages will be deleted regardless of their creation time.
  2. Status: Users can also specify the status of the History List messages to be deleted that includes: (Read, Unread, All)
  3. Groups: Users can also specify the user or group by using the Add Members dialog box

How can we purge orphan rows?

To detect orphan rows:

SELECT COUNT(CONTENT_ID) FROM DSSCSCONTENT WHERE SUB_ID = 2048 AND CONTENT_ID NOT IN (SELECT(RESULT_ID) FROM DSSCSMSGINFO);

To purge orphan rows:

DELETE FROM DSSCSCONTENT WHERE SUB_ID = 2048 AND CONTENT_ID NOT IN (SELECT(RESULT_ID) FROM DSSCSMSGINFO);

After upgrading a history list repository from MicroStrategy 9.0.x to MicroStrategy 9.2.x or higher, expired report caches still remain in the DSSCSCONTENT table after they are no longer needed. These report caches cannot be removed after running the Administrative Tasks "Clean History List Database" or "Delete History List Messages."

Causes of orphan rows:

  1. Starting in MicroStrategy 9.2.0, the DSSCSRPTCACH table was introduced as a dedicated table to store report caches only. In previous versions all the MicroStrategy objects related to History list caches management (including report caches) were stored in the DSSCSCONTENT table. During the upgrade of the History List Repository, if the option "Copy History List content" is unchecked, the History List management mechanism will recognize the fact that there are report caches that are contained in the DSSCSCONTENT table and will use the new table DSSCSRPTCACH for all newly created report caches. The History List management mechanism will continue to use the existing report caches in the DSSCSCONTENT table until all of the existing report caches in the DSSCSCONTENT tables are expired. After the report caches used in the DSSCSCONTENT table are expired they will be removed through the Administrative Task "Delete History List Messages." If the history list messages have been manually deleted from the DSSCSMSGINFO table then there will be no association back to the report caches in the DSSCSCONTENT table, therefore they will not be deleted by Administrative Tasks.
  2. There may be other causes

In MicroStrategy Intelligence Server 9.0.2, the scheduled administrative task 'Clean History List database' has been introduced. Below is a list of the operations performed by this task:

  1. Cleans up orphaned rows in the DSSCSCONTENT and DSSCSRPTCACH (new table introduced in MicroStrategy Intelligence Server 9.2.0) table when the Caches for the History List message is deleted first and then History list message itself is deleted from the remote node (cleanup report cache orphan row)
  2. Cleans up History list for those users deleted from Metadata. The old methods of History List deletion (Message Lifetime, Scheduled task) are based on the user being present in the Metadata. It is always possible for a user object to be deleted in 2- tier, without the users History List being cleared, or an administrator may delete a user from the Metadata without remembering to clear the users History List messaged as well. In this scenario, the users History List message will remain on disk and the associated “History” cache will never be deleted.
  3. Cleans up History List for those projects deleted from the metadata.

To manually delete orphan rows:

  1. Go to Administration -> Events. Right click and select New. Give the name Cleanup History List
  2. Go to Administration -> Schedules. Right click and select New. Give the name Cleanup History List and select event-trigger
  3. Go to Administration -> Schedules. Right click and select Schedule Administrative Task
  4. Go to Administration -> Events. Right click on Cleanup History List and select Trigger
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License