cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
ibalazs
Level 8
Report Inappropriate Content
Message 1 of 7

Records in OrionSchedulerTaskLog table filled up the DB

Hi all, 

I've recently ran into trouble with an MS SQL Express 2017 server hosting my ePO 5.9 DBs. A previously released Protection Workspace extension filled up the DB quite intensively with unnecessary records. The issue was fixed by upgrading PW extension to the latest available package, but the Server Taksk Log still contains more than 8,7 M items (e.g. Protection Workspace - Master Threat Summary Action / Response. ). Also, this amount is visible via SQL Management Studio by simply counting the records in OrionSchedulerTaskLog table.

Followed instructions  in KB76720, but that did not improve the situation so much. KB92098 does not apply at all, amount of threat events is not an issue. 

Top 10 results: 

OrionSchedulerTaskLogMT 8776673 5345.34 8.29
OrionSnapshot 85 1788.80 0.23
EPOEventsMT 511106 740.61 4.73
OrionSchedulerTaskLogDetailMT 1292671 665.45 0.77
EPExtendedEventMT 49258 73.45 1.02
HIP8_LongTextXlate 26367 25.45 0.30
OrionAuditLogMT 51481 24.01 0.59
EPOPolicySettingValuesMT 55877 22.35 1.15
EPO_CheckResult 4144 21.05 0.73
ePA_UseCaseNumericMetric 82613 10.50 0.60

Until now, I could not figure out how to get rid off all that "ballast". Both ePO's built-in scheduled purge task(s) and stored procedure(s) have either failed or timed out - presumably could not handle this massive volume of data, regardless of resources available for SQL Server. Eg: SP_Task_PurgeTaskLogOlderThan @TargetDate='xxxx-xx-xx' ran for ~50 minutes and was able to delete less than 1000 records. Assuming the same speed (limit) a cleanup would take for as much as ~300 days. But that's not acceptable / reasonable. 

Any hints would be greatly appreciated.

Rgds, 

Balazs

6 Replies
cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 2 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

You can try this script in sql - please verify table name, whether it ends in mt or not before running it.

Purpose:
This script will purge log entries from the OrionSchedulerTaskLog table.

The script commits the changes every 10000 rows to prevent the SQL log file (.ldf) from growing to large.

As written the script purges all events with an event generation time older than 12 months. You can alter
the script to purge events older than a different time period by changing the value of the @DeleteTime
variable declaration at the top of the script.

For example if you wanted the script to purge all events older than 6 months you would change this line:
declare @DeleteTime int = -12

To this:
declare @DeleteTime int = -6
*/

-- Delete all events older than the absolute value of the @DeleteTime variable. By default 12 months.
-- Modify this value to change the purge period.
declare @DeleteTime int = -12

-- Purge the OrionSchedulerTaskLog table
SET rowcount 10000
DELETE FROM OrionSchedulerTaskLog
WHERE StartDate < DATEADD(MM, @DeleteTime, GETDATE())
WHILE @@rowcount > 0
BEGIN
DELETE FROM OrionSchedulerTaskLog
WHERE StartDate < DATEADD(MM, @DeleteTime, GETDATE())
END
SET rowcount 0
GO

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 3 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

And it may take a while to run, so let it run overnight as long as it takes.

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

ibalazs
Level 8
Report Inappropriate Content
Message 4 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

Thank you, 

I'll run it ASAP. Probably this is going to be an overnight journey but I look forward to come back with some good news tomorrow. 

cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 5 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

Be sure to adjust the retention period, as that retains a year of events.

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

ibalazs
Level 8
Report Inappropriate Content
Message 6 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

I adjusted the retention period accordingly, but the script ended up with an error in a few seconds:

"Msg 547, Level 16, State 0, Line 5
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint "FK_OrionSchedulerTaskLog_OrionSchedulerTaskLog". The conflict occurred in database "ePO_EPODBNAME", table "dbo.OrionSchedulerTaskLogMT", column 'ParentId'.
The statement has been terminated."

cdinet
McAfee Employee
McAfee Employee
Report Inappropriate Content
Message 7 of 7

Re: Records in OrionSchedulerTaskLog table filled up the DB

Ok, try this (adjust date accordingly):

SET rowcount 10000
DELETE FROM orionschedulertasklogmt
WHERE startdate < '2020-07-01'
WHILE @@rowcount > 0
BEGIN
DELETE FROM orionschedulertasklogmt
WHERE startdate < '2020-07-01'
END
SET rowcount 0
GO

 

Was my reply helpful?
If this information was helpful in any way or answered your question, will you please select Accept as Solution in my reply and together we can help other members?

You Deserve an Award
Don't forget, when your helpful posts earn a kudos or get accepted as a solution you can unlock perks and badges. Those aren't the only badges, either. How many can you collect? Click here to learn more.

Community Help Hub

    New to the forums or need help finding your way around the forums? There's a whole hub of community resources to help you.

  • Find Forum FAQs
  • Learn How to Earn Badges
  • Ask for Help
Go to Community Help

Join the Community

    Thousands of customers use the McAfee Community for peer-to-peer and expert product support. Enjoy these benefits with a free membership:

  • Get helpful solutions from McAfee experts.
  • Stay connected to product conversations that matter to you.
  • Participate in product groups led by McAfee employees.
Join the Community
Join the Community