How to purge data from the ePO database using the ePO console
Technical Articles ID:
KB79561
Last Modified: 2023-07-13 10:20:31 Etc/GMT
Last Modified: 2023-07-13 10:20:31 Etc/GMT
Environment
ePolicy Orchestrator (ePO) 5.x
Summary
If your ePO database is large and needs to be reduced in size, you can identify the tables that need to have data purged.
The easiest way to determine 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.
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 change 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 that 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's not possible, make sure it has enough space to hold the largest of the tables listed above.
- Ensure the following:
- 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.
- You've set aside significant time for the upgrade to complete. It might take hours or potentially days, depending on the size of the tables.
Problem
This problem statement only applies if you're upgrading from ePO 5.3.3 to 5.9.0 or later. After you've upgraded to ePO 5.9.0 or later, the ePO DB schema has been migrated. Subsequent upgrades don't 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 a significant amount of disk space on the SQL Server, and migrating this data might 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 the following tables:
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:
Let the installation continue after the warning in the following scenarios:
A pre-upgrade check during the ePO 5.x upgrade displays a warning message similar to the following:
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 a significant amount of disk space on the SQL Server, and migrating this data might 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 the following tables:
EPOEvents EPOProductEvents EPORollup_Events EPORollup_ProductEvents OrionSchedulerTaskLogDetail OrionAuditLog
- 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.
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. | 60 | 2 hours 15 minutes | 15 GB | 36 GB | |
| Large Audit Log count. Addition of int column with default constraint. | 14.5 | 23 minutes | 5 GB | 5 GB |
Let the installation continue after the warning in the following scenarios:
- When you've performed the steps above
- When you have adequate disk space on the SQL Server for the upgrade to occur
Solution 1
How to purge the threat event log
This solution applies if you need to remove data from the following tables:
This solution applies if you need to remove data from the following tables:
ATD_Events DC_OSS_Events EPCertEventMT EPExtendedEventMT EPOEvents EPStoryGraphInfoMT HIP8_EventInfo HIP8_IPSEventParameter JTIClientEventInfo MVEDRCustomEventMT MVIS_EP_ExtendedEventMT SCOR_Events VSECustomEvent WP_EventInfoMT
- 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 company's 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 non-peak time such as 1:00 a.m. often works well.
- Click Next and then Save.
- Click Run next to the task you created.
Solution 2
How to purge the server task log
The solution below applies when you need to remove data from the following tables:
The solution below applies when you need to remove data from the following tables:
OrionSchedulerTaskLog OrionSchedulerTaskLogDetail EPOCoreLogMessage EPORepositoryLogMessage
- 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 company's 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 non-peak time such as 1:00 a.m. often works well.
- Click Next and then Save.
- Click Run next to the task you created.
Solution 3
How to purge the audit log
This solution applies when you need to remove data from theOrionAuditLog table.
Here are instructions on how to purge server task log entries older than a specified time frame:
This solution applies when you need to remove data from the
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 company's 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 non-peak time such as 1:00 a.m. often works well.
- Click Next, and then Save.
- Click Run next to the task you created.
Solution 4
How to purge Product Events
This solution applies when you need to remove data from the following tables:
This solution applies when you need to remove data from the following tables:
EPOProductEventsMT EPEEventParameters
- 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 company's 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 non-peak time such as 1:00 a.m. often works well.
- Click Next, and then Save.
- Click Run next to the task you just created to execute it.
Solution 5
How to purge rollup data
This solution applies when you need to remove data from the following tables:
This solution applies when you need to remove data from the following tables:
EPORollup_Events EPORollup_ProductEvents
- 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 company's 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 company's 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 non-peak time such as 1:00 a.m. often works well.
- Click Next, then Save.
- Click Run next to the task you just created to execute it.
Solution 6
Shrinking the database
It's important to note that purging data doesn't 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 isn't needed. Consider the following before proceeding:
- Shrinking the ePO database isn't 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.
- Your database has grown unusually large for a reason that you've since corrected.
- You need to perform a one-time shrink operation to get the database back down to the normal size.
- Open SQL Server Management Studio.
- Open a query window and select the database you're trying to shrink. For detailed instructions, see KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database.
NOTE: Most tables reside in the primary ePO database, but in ePO 5.10, theePOEvents 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, see KB76720 - How to purge data from the ePO database without using the ePO console.
Affected Products
Languages:
This article is available in the following languages:
