2 Replies Latest reply on Feb 8, 2011 5:39 AM by mjmurra

    SQL scripts to determine (and purge) highest database usage

      I know this has been posted here before.... But can't easily find it.

       

      What's the easiest SQL script to run to determine which Event ID is causing huge database size issues?

      What's the easiest way to purge the Event ID/Events causing large database issues?

        • 1. Re: SQL scripts to determine (and purge) highest database usage
          JoeBidgood

          mjmurra wrote:

           

          I know this has been posted here before.... But can't easily find it.

           

          What's the easiest SQL script to run to determine which Event ID is causing huge database size issues?

           

          Something like this should do the trick - it'll give the top 5 most common events:

           

              select top 5 epoevents.threateventid as 'Event ID',
              count(*) as 'Count'
              from epoevents
              group by threateventid
              order by count(*) desc

           

           

          What's the easiest way to purge the Event ID/Events causing large database issues?

           

          Easiest way is probably to write a query to return the event ids you're interested in, and then run this query from a "Purge Threat Events" server task.

           

          HTH -

           

          Joe

          • 2. Re: SQL scripts to determine (and purge) highest database usage

            Thanks once again Joe!

             

            Top eventID at this site has 3.1 million entries in the database (failure to scan encrypted file)....  Second top is 1 million (Would be blocked by AP)

             

            There definately needs some selective purging done on the database, and some event filtering implemented.

             

            BTW - does anyone know how much space (on average) an event uses in a SQL database? How much should the database reduce by after removing 3.1 million events and then performing maintenance?


             

             

            Message was edited by: mjmurra on 8/02/11 9:39:15 PM