If you have access to the ePO console, we highly recommend that you purge data from your ePO database.
For details, see related article
KB79561 - How to purge data from the ePO database using the ePO console.
This article must be followed only if you can't purge the data using the database maintenance tasks provided in the ePO console.
If your ePO database is large and you need to reduce the size, determine which tables you need to purge.
For help with the steps below, see
KB67591 - How to run a SQL script provided by Technical Support against the ePolicy Orchestrator database.
Query the ePO database
You can query the ePO database directly to determine the largest tables using the following steps:
- Use SQL Server Management Studio to open a query window, and select the ePO database.
- Run the following script, and analyze the results. This script returns the following:
- The tables in ePO.
- How many rows are in each table.
- How much space in megabytes each table takes up. The script lists the tables by size in desending order:
NOTE: If you’re using ePO 5.10, you must run this script against both ePO databases.
select t.[Name] AS TableName, p.[rows],
CAST(ROUND(((SUM(a.total_pages) * 8) / 1024.00), 2) as numeric(36, 2))
as TotalSpaceMB,
CAST(ROUND(((SUM(a.total_pages) - SUM(a.used_pages)) * 8) / 1024.00, 2)
as numeric (36, 2)) as UnusedSpaceMB
from sys.tables t inner join sys.indexes i on t.OBJECT_ID = i.object_id
inner join sys.partitions p on i.object_id = p.OBJECT_ID AND i.index_id = p.index_id
inner join sys.allocation_units a on p.partition_id = a.container_id
left outer join sys.schemas s on t.schema_id = s.schema_id
where t.[Name] not like 'dt%' AND t.is_ms_shipped = 0 AND i.OBJECT_ID > 255
group by t.[Name], s.[Name], p.[rows]
order by TotalSpaceMB desc, t.[Name];
- Based on the results of the script, you can purge data using one of the appropriate solutions below.
After you purge the data from the relevant tables, the database has more free space. But, this action doesn’t actually reduce the size of the database file on the storage solution. To reduce the database size, you need to shrink the database. But you typically don’t need to shrink the database.
Purge the threat event log
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
The following articles provide detailed instructions on how to purge threat event data using a direct SQL query:
Purge the server task log
This solution applies if you need to remove data from the following tables:
- OrionSchedulerTaskLog
- OrionSchedulerTaskLogDetail
- EPOCoreLogMessage
- EPORepositoryLogMessage
Purge the server task log entries older than a specified time frame:
- Open SQL Server Management Studio.
- Open a query window, and select the ePO database.
- Paste this query into the query window:
exec SP_Task_PurgeTaskLogOlderThan 'mm-dd-yyyyy';
Update the section of the query in single quotes. Replace it with the date for which you want to purge server task log entries.
Example: If you want to purge all server task log entries older than May 1, 2020, the query would look like this:
exec SP_Task_PurgeTaskLogOlderThan '05-01-2020';
- To run the query, click Execute or press F5.
NOTE: If you’re purging lots of data, this query can take many hours to complete.
Purge the audit log
This solution applies if you need to remove data from the
OrionAuditLog table.
To purge server task log entries older than a specified time frame:
- Open SQL Server Management Studio.
- Open a query window, and select the ePO database.
- Paste this query into the query window:
declare @PurgeOlderThanDays int = ##;
--*****Do not edit below this line*****
declare @BatchSize int = 4900, @BatchDelaySeconds int = 3, @RowCount int;
set @RowCount = @BatchSize;
while (@RowCount = @BatchSize) begin
begin transaction;
delete top(@BatchSize) from OrionAuditLog
where EndTime < GetDate() - @PurgeOlderThanDays;
set @RowCount = @@ROWCOUNT;
commit transaction;
if (@RowCount = @BatchSize) waitfor delay @BatchDelaySeconds;
end
go
- Replace ## in the first line of the script, for the number of days you want to retain the audit log entries for.
For example if you wanted to purge all audit log entries older than 90 days the first line would look like this:
declare @PurgeOlderThanDays int = 90;
- To run the query, click Execute or press F5.
NOTE: If you’re purging lots of data, this query can take many hours to complete.
Purge Product Events
This solution applies if you need to remove data from the following tables:
- EPOProductEventsMT
- EPEEventParameters
To purge product events older than a specified time frame:
- Open SQL Server Management Studio.
- Open a query window, and select the ePO database.
- Paste this query into the query window:
declare @PurgeOlderThanDays int = ##;
-- DO NOT EDIT BELOW THIS LINE
declare @BatchSize int = 4900, @BatchDelaySeconds int = 3, @rowcount int = 1;
while (@rowcount > 0)
begin
begin transaction;
delete top(@BatchSize) from EPOProductEventsMT
where ReceivedUTC < GetDate() - @PurgeOlderThanDays;
set @rowcount = @@ROWCOUNT;
commit transaction;
If @rowcount > 0 waitfor delay @BatchDelaySeconds;
end go
- To indicate the number of days for which you want to retain product events, replace ## in the first line of the script.
For example, if you want to purge all product events older than 90 days, the first line would look like:
declare @PurgeOlderThanDays int = 90;
- To run the query, click Execute or press F5.
NOTE: If you’re purging lots of data, this query can take many hours to complete.
Shrink the database
IMPORTANT: When you purge data, it doesn’t actually reduce the database size on disk. To reduce the size, you need to run a shrink command against the database. Normally, you don’t need to run a shrink operation on the ePO database. Consider the following before you continue:
- 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.
Use the following instructions if:
- Your database has grown unusually large for a reason that you’ve since corrected.
And
- You need to do a one-time shrink operation to get the database back down to normal size.
- Use SQL Server Management Studio to open a query window, and select the database you’re trying to shrink.
NOTE: Most tables reside in the primary ePO database. But in ePO 5.10, the ePOEvents table (only) is included in the events database.
- Paste the following SQL statement into the query window:
WARNING: The SQL transaction log (.ldf) file 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.