Loading...

Knowledge Center


How to remove old events and shrink the ePolicy Orchestrator database
Technical Articles ID:   KB68961
Last Modified:  8/7/2019
Rated:


Environment

McAfee ePolicy Orchestrator (ePO) 5.x

Microsoft SQL Server Express

For details of ePO supported environments, see KB51569.

Problem

The Microsoft SQL database has reached the allowed maximum size. There is not enough space remaining to write new data or temporary data needed to perform certain functions. 

The database often reaches a practical limit at just under the stated size limit for SQL Server.

Note the following limits:
  • The limit for SQL Server 2008 R2 Express and SQL Server 2012 Express is 10 GB.
  • The limit for SQL Server 2005 Express and SQL Server 2008 Express is 4 GB.

Solution

To create space within the ePO SQL database, remove older events.

The following steps are a manual way to clean up the ePO database. They do not replace the purge function provided by ePO. For more details, see the ePO product documentation. 
 
NOTES:
  • Take a full backup of the ePO database before you make any changes. For instructions, see KB66616.
  • If Application Control is installed, any events that have not been remediated are removed from both the ePO_event and scor_events tables.
  • You can obtain the server, instance, and database name information needed from the ePO configuration page at: https://localhost:8443/core/config-auth
Step 1 - Remove older events from the database with the following case-sensitive OSQL commands.
 
  1. Click Start, Run, type cmd, and then click OK.
  2. To remove old events, type the following command and press Enter:

    osql -E -S Server_NameInstance -d ePO_Database_Name
     
  3. At the command prompt, type each of the following commands and press Enter:

    DELETE FROM ePOevents WHERE DetectedUTC < 'yyyy-mm-dd 00:00:00.000'
    GO

    NOTE: Make sure that you change yyyy-mm-dd to the appropriate date. Everything earlier than the date you specify is deleted.
 
Step 2 - Open SQL Server Management Studio or SQL Server Management Studio Express and log on with Windows Authentication.
  1. Expand the DATABASE node in the Object Browser window.
  2. Select the ePO database
  3. Click New query, and paste the appropriate query below. The query deletes the events without filling up the log file if hard drive space is low.
ePO 5.10.x - The following query is applicable only from ePO 5.10.x to purge the Event ID 1092:
 
DECLARE @Batch int = 100000;
 
CREATE TABLE #PurgeEvents
(
       AutoID bigint
)
 
DELETE TOP(@Batch) dbo.EPOEvents
OUTPUT deleted.AutoID
INTO #PurgeEvents
WHERE ThreatEventID = 1092;
 
DELETE A
FROM dbo.EPOEventsReference A JOIN #PurgeEvents B
ON A.AutoID = B.AutoID;
 DROP TABLE #PurgeEvents
;

ePO 5.10.x - The following query is only applicable from ePO 5.10.0 to purge the event IDs with date:
 
DECLARE @Batch int = 100000;
 CREATE TABLE #PurgeEvents
(
       AutoID bigint
)
 DELETE TOP(@Batch) dbo.EPOEvents
OUTPUT deleted.AutoID
INTO #PurgeEvents
WHERE ThreatEventID IN (1092,1095) AND ReceivedUTC < '2019-01-01'
 
DELETE A
FROM dbo.EPOEventsReference A JOIN #PurgeEvents B
ON A.AutoID = B.AutoID;
DROP TABLE #PurgeEvents;
 
ePO 5.9.x - Purge the event IDs with date:
 
SET rowcount 10000
DELETE FROM epoEventsMT
WHERE detectedutc < 'yyyy-mm-dd'
WHILE @@rowcount > 0
BEGIN
DELETE FROM epoEventsMT
WHERE detectedutc < 'yyyy-mm-dd'
END
SET rowcount 0
GO
 
IMPORTANT: Make sure that you change yyyy-mm-dd to the correct date. Everything earlier than the date you specify is deleted.

Step 3 - Shrink the database:

NOTES:
  • 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.
     
  1. Click Start, Run, type cmd, and then click OK.
  2. Type the following command and press Enter:

    osql -E -S <server_nameinstance> 
     
  3. At the command prompt, type each of the following commands and press Enter after each:

    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_Server_Name')
    go
     
  4. To determine the amount of free space within the database, you can use the following command from SQL Server Management Studio on the ePO database: Exec sp_spaceused.

    For more details about the sp_spaceused command, see SQL Server Books Online documentation.

Rate this document

Beta Translate with

Select a desired language below to translate this page.

Glossary of Technical Terms


 Highlight Glossary Terms

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