Purge Server Task Log SQL Command

[Aj-richer]

Hi All,

Having problems at the moment attempting to purge table dbo.OrionSchedulerTaskLogMT from the ePO side.  This just runs indefinitly without purging anything even if i create a new task and caused the web console to hang / crash.  Other things are now starting to be affected so i guess indexing needs to be completed also.

Please could someone help by providing the SQL Commands that i can pass on to our dbo here to allow him to 

• Purge the Table mentioned (or any table if possible with a small change) to X of days or Date.
• Command to re-index the database once done.
• Anything else i may be missing?

ePO Version is 5.9.0 (waiting for certificates to update so patching to 5.9.1 not available at this time).

Many thanks in advance

Aj

[cdinet]

Reindex script is attached to KB67184 - you have to be logged into service portal to view that.

Script to purge events from the server task log table - please verify table name, as it may or may not end in MT.  You can change the date as desired.

SET rowcount 10000
        DELETE FROM OrionSchedulerTaskLogMT
        WHERE StartDate < '2019-01-01'
        WHILE @@rowcount > 0
        BEGIN
        DELETE FROM OrionSchedulerTaskLogMT
        WHERE StartDate < '2019-01-01'
        END
        SET rowcount 0

[cdinet]

Actually I spoke before the query finished on my test server.  I got this error:

The DELETE statement conflicted with the REFERENCE constraint "FK_OrionSchedulerTaskLogDetail_TopLevelTaskLogId_OrionSchedulerTaskLog". The conflict occurred in database "ePO_xxxx", table "dbo.OrionSchedulerTaskLogDetailMT", column 'TopLevelTaskLogId'.

The statement has been terminated.

I can private message you a script to truncate the table - I don't have one to purge it that won't run into the constraints.

[cesaro]

Can you send me the script to truncate the table ?

[cdinet · Accepted solution]

KB83652 has a script that can be modified based on the correct table names and columns in that table.  For the task log table, you would use the following, changing date to desired time frame.  For the reindex, run the script that is attached to kb67184.

SET rowcount 10000
DELETE FROM orionschedulertasklogmt
WHERE startdate = '2019-05-01'
WHILE @@rowcount > 0
BEGIN
   DELETE FROM orionschedulertasklogmt
   WHERE startdate = '2019-05-01'
END
SET rowcount 0