How to remove old events and shrink the ePolicy Orchestrator database

How to remove old events and shrink the ePolicy Orchestrator database

Environment
McAfee ePolicy Orchestrator (ePO) 5.x
 
 
Microsoft Windows Server 2008 R2
Microsoft Windows Server 2008
Microsoft Windows Server 2003

Microsoft SQL Server 2012 Express
Microsoft SQL Server 2008 R2 Express
Microsoft SQL Server 2008 Express
Microsoft SQL Server 2005 Express

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. The limit for SQL Server 2005 Express and SQL Server 2008 Express is 4 GB. The limit for SQL Server 2008 R2 Express and SQL Server 2012 Express is 10 GB.

Solution

Remove older events to create space within the ePO SQL database.

The following steps are an occasional way to clean up the ePO database. They do not replace the purge functionality provided by ePO. For more details, refer to 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.
  • Where ePO_<servername> is used in the following steps, substitute the name of the ePO database without the chevrons (<>).
  • You can obtain the server, instance, and database name information required from the ePO configuration page at: https://localhost:8443/core/config-auth.
     
  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. Type the following command and press ENTER:

      osql -E -S <servername\instance> -d ePO_<databasename>
       
    3. At the command prompt, type each of the following commands and press ENTER after each command:

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

      NOTE: Ensure that you change yyyy-mm-dd to the appropriate date; everything earlier than the date you specify will be deleted.
       
    4. If applicable, launch SQL Server Management Studio or SQL Server Management Studio Express installed and log in with Windows Authentication.
    5. Expand the DATABASE node in the Object Browser window, click the ePO database, click new query, and paste the appropriate query below. This deletes the events without filling up the log file if hard drive space is low.

      ePO 5.3.x and later:
      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

      ePO 5.1.x and earlier:
      SET rowcount 10000
      DELETE FROM epoEvents
      WHERE detectedutc < 'yyyy-mm-dd'
      WHILE @@rowcount > 0
      BEGIN
      DELETE FROM epoEvents
      WHERE detectedutc < 'yyyy-mm-dd'
      END
      SET rowcount 0
      GO

      IMPORTANT: Ensure that you change yyyy-mm-dd to the correct date; everything earlier than the date you specify will be deleted.
       
  2. Shrink the database:

    NOTES:
    • Shrinking the ePO database is not specifically recommended or required 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 accomodate new data, the SQL server will lock the files for the duration of the growth. This can result in performance issues with the application that uses the database while the new data is inserted.
       
    1. Type the following command and press ENTER:

      osql -E -S <servername\instance> 
       
    2. 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. Ensure that you have sufficient free disk space before you use this command.

      dbcc shrinkdatabase ('ePO_<servername>')
      go
       
  3. 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 additional details on the sp_spaceused command, refer to SQL Server Books Online documentation.


Disclaimer

The content of this article originated in English. If there are differences between the English content and its translation, the English content is always the most accurate. Some of this content has been provided using Machine Translation translated by Microsoft.