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:

1. Use SQL Server Management Studio to open a query window, and select the ePO database.
2. 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 descending 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];
      
3. 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:

• KB68961 - How to find the top 10 threat events and purge events – Use this article to purge threat event data based on a specific EventID, which is filling up the database.
• KB92098 - How to purge large amounts of event data from ePolicy Orchestrator using an SQL query – 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:

1. Open SQL Server Management Studio.
2. Open a query window, and select the ePO database.
3. Paste this query into the query window:
   
   exec SP_Task_PurgeTaskLogOlderThan 'mm-dd-yyyyy';

3. To run the query, click Execute or press F5.
   
   NOTE: If you’re purging lots of data, this query can take many days 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:

1. Open SQL Server Management Studio.
2. Open a query window, and select the ePO database.
3. Paste this query into the query window:​

4. 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;
    
5. To run the query, click Execute or press F5.
   
   NOTE: If you’re purging lots of data, this query can take many days 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:

1. Open SQL Server Management Studio.
2. Open a query window, and select the ePO database.
3. Paste this query into the query window:​

4. 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;
    
5. To run the query, click Execute or press F5. 
   
   NOTE: If you’re purging lots of data, this query can take many days to complete.

Purge Rollup Threat Events
This solution applies if you want to purge data from the following tables:

• EPORollup_Events
• ENSRollup_JTClientEventInfo
• ENSRollup_WP_EventInfo
• ESPRollup_EPExtendedEvent

To purge rollup threat events older than a specified time frame, perform the following steps:

1. Open SQL Server Management Studio.
2. Open a query window, and select the ePO database.
3. Paste this query into the query window:​

4. 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;
    
5. To run the query, click Execute or press F5. 
   
   NOTE: If you're purging lots of data, this query can take many days 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.

Follow the instructions below in the following scenarios:

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

1. Use SQL Server Management Studio to open a query window, and select the database that 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.
    
2. 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
    
3. Click Execute or press F5.