Mcafee/Trellix EPO Database SQL shrink Size in small steps via OSQL

For all DBA’s > Yes we know but Mcafee tells us to shrink 😉
Also see our security concerning regarding centralizes SQL for Security Appliances/Servers.

Problem:

You are unable to shrink the Mcafee EPO SQL Database with Management Studio (GUI). You are already on “SIMPLE RECOVERY” mode for the DB.

Management thoughts:

There is base discussion, which comes from real world outbreak experience on:

a)    Keep SQL on EPO Server with SQL Express with XXGB limit

OR

b)    Put all in a centralized large SQL-Cluster

c)    Install FULL licence SQL on EPO

Why?

The problem in an outbreak maybe the EPO can’t reach the dedicated SQL WHICH by the way is anyway under the ATTACK prio 1 in today’s ransomware. So we keep the SQL local. Now because ALL have no money we often use SQL Express.

This works just perfect UNTIL you reach the SQL Express limit and that only happens with FALSE Positive or when a real outbreak is there. You can filter what the Agent send to the EPO for that reason. Mcafee is often the #1 itself with Product a alerting mcafee product b as false/positive. Like the change they had with P9 for VSE 8.8

Solution:

Here is how to SHRINK the DB and Transaction LOG in smaller little steps so the TEMP file he needs for this does not overgrow. Sometimes this seems to be needed to overcome also the SQL Express limit which is at 10GB for 2008R2 Express.

1)    Make sure you have at least the same SIZE you have in DB on the Disk > Resize the VM EPO C: or d: drive in VM and Extend in FDISK.

2)    Check Locks (Maybe better than rebooting the EPO Server in such a situation)

select cmd,* from master..sysprocesses where db_name(dbid)=’ePO4_CUSEPO3′

Check the EPO Database size on SQL-Express. Once again you need space to shrink it. If NOW do the Steps mention in here with really small values (Like: dbcc shrinkfile(ePO4_CUSEPO3_log, 100) < 100MB each time.

Use this to see the physical filenames of the SQL-DB you need shrink via CLI.

sp_helpfile

Samples filenames:

EPO4_CUSEPO3

EPO4_CUSEPO3_log

Then do following to shrink the Logfiles in 100MB=1GB step (Repeat a few times)

use ePO4_CUSEPO3

go

dbcc shrinkfile(ePO4_CUSEPO3_log, 1000)

use ePO4_CUSEPO3

go

dbcc shrinkfile(ePO4_CUSEPO3, 1000)

Please notice THAT some TABLES changed from EPO 5.X to 5.2/5.3 this may be the reason you landed on this post finally.

Here our sample scripts we use to keep customer EPO’s small.

UNTIL EPO 5.1 < Important!

use EPO4_CUSepo3

go

DELETE FROM EPOEvents WHERE (DetectedUTC < GETDATE() – 30)

go

DELETE FROM OrionAuditLog WHERE (StartTime < GETDATE() – 30)

go

DELETE FROM OrionSchedulerTaskLog WHERE (StartDate < GETDATE() – 30)

go

DELETE FROM OrionSchedulerTaskLogDetail WHERE (MessageDate < GETDATE() – 30)

go

DELETE FROM DLP_EventInfo WHERE (InsertionTime < GETDATE() – 90)

go

Use master

GO

ABOVE/FROM EPO5.3 ON I think the tables changed!

use EPO4_CUSepo3

go

DELETE FROM epoEventsMT WHERE (DetectedUTC < GETDATE() – 15)

go

DELETE FROM EPOProductEventsMT WHERE (DetectedUTC < GETDATE() – 15)

go

DELETE FROM OrionAuditLogMT WHERE (StartTime < GETDATE() – 15)

go

DELETE FROM OrionSchedulerTaskLogMT WHERE (StartDate < GETDATE() – 15)

go

DELETE FROM OrionSchedulerTaskLogDetailMT WHERE (MessageDate < GETDATE() – 15)

go

DELETE FROM DLP_EventInfo WHERE (InsertionTime < GETDATE() – 90)

go

Use master

GO