This content has been marked as final. Show 2 replies
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.
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 :P 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]
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
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.