2 Replies Latest reply on Jan 9, 2009 6:58 AM by Turift

    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
        • 1. Event log size !!?
          SergeM
          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
          • 2. 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 :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]
            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.