If you have access to the ePO console, we highly recommend that you follow the steps in
KB79561 to purge data from your ePO database.
This article must be followed only if you can't purge the data using the maintenance tasks provided in the ePO console.
If your ePO database is large and you need to reduce the size, you first need to determine which tables you need to purge.
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. For instructions, see KB67591.
- 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 are 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 does not 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 do not 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
- EPOEvents
- EPCertEventMT
- EPExtendedEventMT
- HIP8_EventInfo
- HIP8_IPSEventParameter
- JTIClientEventInfo
- VSECustomEvent
- WP_EventInfoMT
The following articles provide detailed instructions on how to purge threat event data using a direct SQL query:
- KB68961 – Use this article to purge threat event data based on a specific EventID, which is filling up the database.
- KB92098 – Use this article if you need to purge threat event data based on criteria other than EventID.
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. For instructions, see KB67591.
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 are 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. For instructions, see KB67591.
- Paste this query into the query window:
declare @PurgeOlderThanDays int = ##;
--*****Do not edit below this line*****
declare @BatchSize int = 10000, @BatchDelaySeconds int = 5, @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 are 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. For instructions, see KB67591.
- Paste this query into the query window:
declare @PurgeOlderThanDays int = ##;
-- DO NOT EDIT BELOW THIS LINE
declare @BatchSize int = 1000, @BatchDelaySeconds int = 5, @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 are purging lots of data, this query can take many hours to complete.
Shrink the database
IMPORTANT: When you purge data, it does not actually reduce the database size on disk. To reduce the size, you need to run a shrink command against the database. Normally, you do not need to run a shrink operation on the ePO database. Consider the following before you continue:
- 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 that you have 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 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) 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.
