How to purge data from the ePO database using the ePO console

Technical Articles ID: KB79561
Last Modified: 6/25/2020

Environment

McAfee ePolicy Orchestrator (ePO) 5.x

Summary

If your ePO database is large and needs to be reduced in size, determine which tables need to have some data purged.

The easiest way to determine which are the largest tables is to look in the ePO console:

Log on to the ePO console.
Navigate to Menu, Configuration, Server Settings.
Select Server Information.
Scroll down, and locate the section labeled, Largest Tables.

The Largest Tables section of the Server Information page displays the top 20 largest tables in the ePO database in desending order. When you know which tables have the most data, you can use the appropriate solution below.

Prepare the SQL Server:
Before implementing any of the solutions below, or making an ePO upgrade, you might need to prepare SQL. If your company has a database administrator (DBA) who manages the SQL Server, DBA involvement is likely needed for the following steps:

If you changed the database to Full Recovery Mode (default is Simple), perform a backup first. Then set it to Simple Recovery Mode. This option cleans the transaction log, and limits the growth of the transaction log size.
Make sure the disk volume that contains the transaction log has extra free space. The amount of free space needs to be greater than the size of the ePO database MDF file. If that is not possible, make sure it has enough space to hold the largest of the tables listed above.
Make sure:
- The transaction log is set to Auto-grow.
- The disk volume that contains the ePO database MDF file has significant free space for duplicating your largest tables.
- That you have set aside significant time for the upgrade to complete. It could take hours or potentially days, depending on the size of the tables.

Problem

This problem statement only applies if you are upgrading from ePO 5.3.3 to ePO 5.9.0 or later. After you have upgraded to ePO 5.9.0 or later, the ePO DB schema has been migrated. Subsequent upgrades do not require the tables in question to be updated.

A pre-upgrade check during the ePO 5.x upgrade displays a warning message similar to the following:

The SQL database needs more free space or setup will fail.

It needs two times the current database size to continue.

The ePO 5.x installation makes a schema change on these tables. This change could take up significant more disk space on the SQL Server, and migrating this data could take a long time to complete.

The ePO 5.x installer looks at the size of unbounded tables in the ePO database.

When you upgrade from the 5.x version, it verifies only a subset of the following tables. But, when you upgrade from earlier 4.x versions, it includes checks for all following tables:

EPOEvents
EPOProductEvents
EPORollup_Events
EPORollup_ProductEvents
OrionSchedulerTaskLogDetail
OrionAuditLog

NOTES:

If the ePO installer changes any of the tables listed above, and they have over one million rows, a warning message is displayed before the installation starts.
The SQL Server needs significant reserve disk space to apply the schema changes to these tables.
The changes take significant time to complete, which causes the ePO upgrade to potentially run for many hours or fail.

IMPORTANT: Create a full backup of the ePO server and database as directed in the release notes.

The time and space needed are directly proportional to the number of rows in the table.

Supporting data:
The following data comes from our internal testing of the ePO 5.x table schema upgrade.

SQL Server specifications:

Single SATA 2 spindle
4 Core 2.8 GHz Xeon
LDF and MDF on the same disk. The MDF file is the primary file in SQL Server database. The LDF is a supporting file.

Test	Table	Row Count (million)	Time	MDF Growth	LDF Growth
Large Client Event count. Modify Identity column while preserving time stamps.	EPOProductEvents	60	2 hours 15 minutes	15 GB	36 GB
Large Audit Log count. Addition of int column with default constraint.	OrionAuditLog	14.5	23 minutes	5 GB	5 GB

Let the installation continue after the warning:

When you have performed the steps above
And
Have adequate disk space on the SQL Server for the upgrade to occur

Solution

How to purge the threat event log
This solution applies if you need to remove data from the following tables:

ATD_Events
EPOEvents
EPCertEventMT
EPExtendedEventMT
HIP8_EventInfo
HIP8_IPSEventParameter
JTIClientEventInfo
VSECustomEvent
WP_EventInfoMT

You have many options for selecting which events to purge. Here are instructions on how to purge events older than a specified time frame:

Log on to the ePO console.
Navigate to Menu, Automation, Server Tasks.
Click New Task.
Give the task a name, and click Next. For example, you might name the task Threat Events Purge.
Select Purge Threat Event Log from the Actions drop-down list.
Enter information in the Purge records older than radial selection. Which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click Next.
Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
Click Next then Save.
Click Run next to the task you created.

Solution

How to purge the server task log
This solution below applies when you need to remove data from the following tables:

OrionSchedulerTaskLog
OrionSchedulerTaskLogDetail
EPOCoreLogMessage
EPORepositoryLogMessage

To purge server task log entries older than a specified time frame:

Log on to the ePO console
Navigate to Menu, Automation, Server Tasks.
Click New Task.
Give the task a name and click Next. For example, you might name the task Server Task Log Purge.
Select Purge Server Task Log from the Actions drop-down list.
Enter information in the Purge records older than radial selection which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click Next.
Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
Click Next, then Save.
Click Run next to the task you created.

Solution

How to purge the audit log
This solution applies when you need to remove data from the OrionAuditLog table.

Here are instructions on how to purge server task log entries older than a specified time frame:

Log on to the ePO console.
Navigate to Menu, Automation, Server Tasks.
Click New Task.
Give the task a name and click Next. For example, you might name the task Audit Log Purge.
Select Purge Audit Log from the Actions drop-down list.
Enter information in the Purge records older than radial selection which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click Next.
Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
Click Next, then Save.
Click Run next to the task you created.

Solution

How to purge Product Events
This solution applies when you need to remove data from the following tables:

EPOProductEventsMT
EPEEventParameters

To purge product events older than a specified time frame:

Log on to the ePO console.
Navigate to Menu, Automation, Server Tasks.
Click New Task.
Give the task a name, and click Next. For example, you might name the task Purge Client Events.
Select Purge Client Events from the Actions drop-down list.
Enter information in the Purge records older than radial selection which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click Next.
Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
Click Next, then Save.
Click Run next to the task you just created to execute it.

Solution

How to purge rollup data
This solution applies when you need to remove data from the following tables:

EPORollup_Events
EPORollup_ProductEvents

To roll up events older than a specified time frame:

Log on to the ePO console.
Navigate to Menu, Automation, Server Tasks.
Click New Task.
Give the task a name and click Next. For example, you might name the task Rollup Events Purge.
Select Purge Rolled-up Data from the Actions drop-down list.
Select Threat Events from the Data Type drop-down list.
Enter information in the Purge records older than radial selection which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click the + sign on the right side of the Actions line.
Select Purge Rolled-up Data from the new Actions drop-down list.
Select Client Events from the Data Type drop-down list.
Enter information in the Purge records older than radial selection which conforms with your companies data retention policy. If you don't have one, try using 90 days.
Click Next.
Schedule the task to run regularly. For example, once a day at a nonpeak time such as 1 a.m. often works well.
Click Next, then Save.
Click Run next to the task you just created to execute it.

Solution

Shrinking the database

It is important to note that purging data does not actually reduce the database size on disk. To complete that task, you must run the shrink command against the database. Normally a shrink operation on the ePO database is not needed. Consider the following before proceeding:

Shrinking the ePO database is not recommended or needed for ePO to function.
Shrinking the database files (.MDF) and (.NDF) can increase index fragmentation and cause queries to run slowly.
After you shrink the database files, if the database needs to expand to accommodate new data, the SQL Server locks the files during the growth. The result can be performance issues with the application that uses the database while the new data is inserted.

Use the following instructions if:

Your database has grown unusually large for a reason which you have since corrected.
And
You need to do a one-time shrink operation to get the database back down to normal size.

Open SQL Server Management Studio.
Open a query window and select the database you are trying to shrink. For detailed instructions, see KB67591.
NOTE: Most tables reside in the primary ePO database, but in ePO 5.10 the ePOEvents table (only) is located in the events database.
Paste the following SQL statement into the query window:

WARNING: The SQL transaction log (.LDF) can grow up to five times the size of the database while running the command. Make sure that you have sufficient free disk space before you use this command.

dbcc shrinkdatabase ('ePO_DatabaseName')
go

Click Execute, or press F5.

Related Information

This article only applies if you have access to your ePO console. If you can't access the ePO console because the database is full, you can follow the instructions in KB76720 to remove data from the ePO database.

Affected Products

ePolicy Orchestrator 5.9
ePolicy Orchestrator 5.10.x
Upgrade/Migrate

Languages:

This article is available in the following languages:

German
English United States
Spanish Spain
French
Italian
Japanese
Korean
Dutch
Portuguese Brasileiro
Chinese Simplified
Chinese Traditional

Glossary of Technical Terms

Highlight Glossary Terms

Please take a moment to browse our Glossary of Technical Terms.

Knowledge Center

How to purge data from the ePO database using the ePO console

Environment

Summary

Problem

Solution

Solution

Solution

Solution

Solution

Solution

Related Information

Affected Products

Languages:

Glossary of Technical Terms

Title

Title

Knowledge Center

How to purge data from the ePO database using the ePO console

Environment

Summary

Problem

Solution

Solution

Solution

Solution

Solution

Solution

Related Information

Affected Products

Languages:

Glossary of Technical Terms

Choose your region

Title

Title