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:
Step 1 - Remove older events from the database with the following
case-sensitive OSQL commands.
- Click Start, Run, type cmd, and then click OK.
- To remove old events, type the following command and press Enter:
osql -E -S Server_NameInstance -d ePO_Database_Name
- 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.
- Expand the DATABASE node in the Object Browser window.
- Select the ePO database
- 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.
- Click Start, Run, type cmd, and then click OK.
- Type the following command and press Enter:
osql -E -S <server_nameinstance>
- 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
- 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.