cancel
Showing results for 
Search instead for 
Did you mean: 
Turift
Level 7
Report Inappropriate Content
Message 1 of 3

Log purge/delete fails

Hi

I'm a bit confused with this one, got a "delete unwanted events withing the last three months" task that runs every hour, but I doesn't delete anything, or more precisely it doesn't delete the events I want anyways. I've made a new query that locates the EventIDs and it runs correctly, but I'm unable to successfully purge those results manually as well.

The SQL db has increased a lot within the last 1-2 months, growing approx. 15GB from 30 to 45GB. I made another query that looks for those EventIDs within the last three months and its at least 16GB the should be deleted.

ePO 4.0 P3

Any ideas?

Thanks
2 Replies
SergeM
Level 9
Report Inappropriate Content
Message 2 of 3

Event log size !!?

Hi,



I've a similar issue with an event log size and a DB that's grown to about 35 GB ! It seems outrageously big and I'm trying to find a way to

  • purge (some) old events
  • reduce growth (I suppose this means reducing the number of logged items in VSE Access Protection)



Thing is, I'm not sure which events to purge. Hell, I'm not sure I even know which events exist! (BTW is there somewhere a list of the different events or am I left with making "event analysis" in my DB ?)

Anyone has best practices rules as to which events to delete with which frequency ?

Presently I'm aiming my gun at "Notices" which constitute 90% of my logs. I'm also wondering what I should log at all.

Serge
Turift
Level 7
Report Inappropriate Content
Message 3 of 3

RE: Event log size !!?

Yepp, you read it correctly. But you failed to read what I meant happy And that was that I got a task named "delete unwanted events within the last three months" that runs every third month. A minor typo Smiley Tongue Don´t know why it's the last three months and not every month or whatever. But that's the case anyway.

So the task is set to delete the events, and runs quiet without error, but fails to delete anything. The task is a Purge event log with the settings purge by query "Unwanted EventIDs".

The way I currently delete the EventIDs is raw sql commands directly in the db, that works, but that isn't the way I want to do it.

The scripts I use for that is these two:
This first one is probably the best, I use another script to delete the events I find that I don't want.

/* Check for the top 10 EventIDs written to the db*/
select top 10 count(*) as 'count', [EPOEvents].[ThreatEventID], [EPOEvents].[analyzer]
from EPOEvents
group by [EPOEvents].[ThreatEventID],(EPOEvents.analyzer) order by [count] desc

and this one

/* Check for top 30 tables*/
DECLARE @LOW INT
SET @LOW = (SELECT LOW FROM master.dbo.spt_values WHERE number = 1 and type= 'E')
SELECT TOP 30 o.[NAME] AS [NAME],
CASE o.xtype WHEN 'U' THEN 'User Table' END AS TYPE , u.[NAME] AS
OWNER, I.[ROWS] ,
CONVERT(DECIMAL (15,2),(I.reserved * (@LOW / 1024.))/1000) AS
RESERVED_MB,
CONVERT(DECIMAL (15,2),(I.used * (@LOW / 1024.))/1000) AS USED_MB,
SUM(CONVERT(BIGINT, i.reserved)* (8192/1000000)) AS VOLUMINOUS
FROM SYSUSERS u
JOIN SYSOBJECTS o ON u.uid = o.uid
LEFT JOIN SYSINDEXES i ON o.id = i.id
WHERE i.indid IN (0,1,255) and o.xtype in ('U','V')
GROUP BY o.[NAME],u.[NAME],o.xtype,I.[ROWS],I.reserved,I.used
ORDER BY RESERVED DESC

Note that the size is more a guideline to the amount allocated, not the exact size. I'm no SQL guru so I can't tell you why because I don't remember why.

The top Event was 1092 in my case, I've stopped logging that entirely, don't want it.
It was like 17 million 1092 events written or so, and that takes a huge amount of free space to delete.

Click Configuration | server settings | Event filtering | edit and you'll get a list of the different EventIDs tha tyou can choose to filter, so they don't get logged.
I just look through that list and decide what I find not so interesting. But I want as much logged as possible so use the script above to check which ids are logged the most and deselect those.