Confluence Installation Guide

1. 

   Oct 08

   Anonymous says:

   Cleaning out the System Tasks Status Log This article was formerly published as...

   Cleaning out the System Tasks Status Log

   This article was formerly published as KB-article 181-2775 in Blackboard's Parature knowledgebase.

   Issue

   The System Tasks Status Log needs regular cleaning.

   Symptoms

   There are many entries listed in the System Tasks Status log, viewed from the system administrator panel. Deleting them manually takes far too long, and entries in "error" status cannot be deleted at all.
   In the performance report on Unix platforms, the "get_next_task" procedure shows up as using significant database resources.

   In Blackboard release 9, a large amount of additional entries with task_type='A' is being produced in this table. This necessitates very frequent cleanup (ideally daily, especially if daily enrollment snapshots are being run). Please see the additional information about [the NotificationRemoveStaleData task]. Also note that due to the large amount of extra entries, the previously suggested index on the STATUS column is no longer sufficient, and a multi-column index on (STATUS,TASK_TYPE) is suggested instead. See below.

   Analysis

   These entries are not needed over time, as they are only useful to determine if a recent course copy or archive restore was executed successfully. If the status is "error", this serves as an alert that you should redo that course copy or additional operation. Having many of these entries leads to performance problems on the system, since the table is checked frequently for any tasks to be executed, and full table scans will be executed every time on the QUEUED_TASKS table.

   Adding an index for performance

   Even if for some reason you don't want to clean out these older records you will still want to avoid the resulting performance hit – and note that even with daily cleaning of the table the number of records in release 9 warrants adding this index. For this purpose every client should add an index on QUEUED_TASKS(STATUS,TASK_TYPE) (and Oracle users need to collect histograms for this column). This will avoid the full table scans.

   Oracle instructions

   Execute these two commands while logged into the BB_BB60 schema via sqlplus:

   CREATE INDEX QUEUED_TASKS_ST ON QUEUED_TASKS(STATUS,TASK_TYPE) TABLESPACE BB_BB60_INDX;
   EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'BB_BB60', tabname=>'QUEUED_TASKS',
   cascade=>TRUE, method_opt=>'FOR COLUMNS STATUS,TASK_TYPE SIZE AUTO');
   

   SQLserver instructions

   • In SQLserver Management Studio, open the BB_BB60 database and select the QUEUED_TASKS table.
   • In the Object Explorer window at right, click on Indexes (or expand QUEUED_TASKS at left and click Indexes below it).
   • Right-click in the background and choose "New Index".
   • Name the index "QUEUED_TASKS_ST".
   • Click on "Add" to specify columns to be indexed.
   • Select the STATUS and TASK_TYPE columns.
   • In the result, TASK_TYPE will be listed above STATUS. Use the MOVE buttons to move STATUS to the top.
   • OK/close the new Index dialog. The index will be created right away.

   Alternative steps:

   • Open the table in design mode, then click on the "Indexes/Keys" button on the toolbar (near right end, it shows a key-and-list icon).
   • click on "Add", then for the newly added index find "General > Columns" and click on the right field (where it currently says "pk1"). Click the ellipsis button ("…") that now shows up at the right.
   • Select the STATUS column in the drop-down list. Leave order as ascending.
   • Click in the empty field below "status" and select TASK_TYPE from the drop-down. Again leave order as ascending. Click "OK".
   • Under "Identity > name", enter QUEUED_TASKS_ST (instead of the suggested "IX_QUEUED_TASKS").
   • Under "Table Designer > Recompute Statistics" select "Yes". Click "Close".
   • Close the table design view and choose to save the design changes (or hit Ctrl-S before closing the view).

   Resolution

   This logging information is kept in the database. You can run the following query which will delete all completed tasks older than 7 days, as well as those in "Error" status, which are not deletable from the GUI. Connect to the BB_BB60 database and run the appropriate version for your database type:

   Oracle

   DELETE FROM queued_tasks WHERE status IN ('C','E') AND end_date < sysdate-7;
   COMMIT;
   

   SQL Server

   DELETE FROM queued_tasks WHERE status IN ('C','E') AND end_date < getdate()-7
   

   You may also want to delete more recent tasks, and besides tasks with 'C' (completed) or 'E' (error, not restartable) status you may want to also delete tasks in 'I' status (incomplete, can be restarted) as well - this is up to the user.

   For a very quick and complete cleanup, check that no tasks are in 'A' (assigned to be run), 'R' (running), or 'W' (waiting) status, then run TRUNCATE TABLE QUEUED_TASKS. The following PL/SQL code does this for you in Oracle:

   DECLARE
   v_count NUMBER;
   BEGIN
   SELECT count(*) INTO v_count FROM QUEUED_TASKS WHERE status IN ('A','R','W');
   IF v_count=0 THEN
   EXECUTE IMMEDIATE 'TRUNCATE TABLE QUEUED_TASKS';
   END IF;
   END;
   /
   

   Also note that occasionally a course copy task may have gotten stuck and may prevent other such tasks from completing - if that happens, try deleting the offending task (first not-completed task) listed in the GUI from the database side and things should work again after a server restart. The task should be identified by a status of 'R' (running) and a task_type of 'C' (course copy).

   Scheduling regular cleanup

   You may want to schedule the above as a regular cleanup task in your SQLserver maintenance plan, or as an Oracle job via some code like this (of course you could schedule this to run less frequently, or to keep more data, or run at a different time/day):

   DECLARE
   JobNo dba_jobs.job%TYPE;
   BEGIN
   --schedule cleanup of QUEUED_TASKS each Sunday morning at 4:30am:
   DBMS_JOB.SUBMIT(JobNo, 'DELETE FROM queued_tasks WHERE status IN (''C'',''E'') AND end_date < sysdate - 7;',
   trunc(sysdate) + 4/24 + 1/48 + 8 - to_char(sysdate,'D'),
   'trunc(sysdate) + 4/24 + 1/48 + 7');
   END;
   /
   

   Unknown macro: {show-to}

   This is former Swiki article [Support/445]

   • Permalink

   • Reply

2. 

   Oct 08

   Anonymous says:

   test

   test

   • Permalink

   • Reply

Add Comment