5 Replies Latest reply on Feb 23, 2011 4:37 AM by mjmurra

    SQL Transaction log issues

      Not really EPO specific, more SQL specific, but hoping for some maintenance ideas.

       

      I have ~40Gb of Epo Client events (Entire DB is about 70GB). Need to delete one particular eventID that takes up about 30Gb of that!

       

      Attempted to run the mass delete via SQL command. This created a huge transaction log file, and end result was that disk only had a couple of Gb left. Had to cancel deletion - otherwise disk space would be zero. Transaction log ended up being circa 40Gb.

       

      Managed to get more disk and attempted the delete again. Again the Transaction log grew and is currently sitting at > 100Gb. Had to cancel the delete again for disk space reasons.

       

      Transaction log is set for basically growth (not sure why). DB itself only grew slightly, but some space was returned within the database itself (~10Gb free space). It has a conservative growth of 5Mb, unlimited, whereas Transaction Log is 10%, max of 2.2Tb! (I think someone in the past didn't think it was Mb - but rather Kb for the size!!)

       

      Am currently running a DB Shrink, but looking for other maintenance tips to run - especially how to purge the Transaction logs of that data. Recovery model is set to "Simple".

        • 1. SQL Transaction log issues
          JoeBidgood

          Basically what's biting you here is the total number of individual transactions - one for each row in the db that needs to be deleted. There are various ways of dealing with this, but the easiest way is probably to do the delete in chunks, such that each chunk is small enough to allow the delete to complete and the transaction to be committed.

          Personally I tend to do this by date - so for example delete everything with the eventid that you're interested in that's older than a year. Once complete run the same query for everything older than ten months, and so on.

           

          HTH -

           

          Joe

          1 of 1 people found this helpful
          • 2. SQL Transaction log issues
            risingflight

            Hello this article below is with respect to EPO, regarding sql i dont know

             

            https://kc.mcafee.com/corporate/index?page=content&id=KB51873

            • 3. SQL Transaction log issues

              So the standard SQL task to delete one particular EventID is  delete from EPOEvents where ThreatEventID='<EventIDCode>' - and the one to delete all events by previous to a date is delete from EPOEvents where DetectedUTC < 'YYYY-MM-DD'

               

              Is the combined query   delete from EPOEvents where ThreatEventID='<EventIDCode>' and  DetectedUTC < 'YYYY-MM-DD' - or something else?

               

               

               

               

               

              • 4. SQL Transaction log issues
                JoeBidgood

                That looks fine

                 

                (If you wanted to delete more than one event ID you could use SQL's " in " operator, thus:

                DELETE FROM ePOEvents WHERE ThreatEventID IN ('event id 1', 'event id 2', 'event id 3')

                 

                Regards -

                 

                Joe

                1 of 1 people found this helpful
                • 5. SQL Transaction log issues

                  Thanks Joe . Will run up the script tomorrow and take monthly bites out of the events.

                   

                  Could someone at McAfee consider this particular information for a potential KB article? Tried searching for information on how to delete by EventID via SQL but could only find references for deleting by Date - and I can't recall any of them warning about potential for SQL log issues from mass deletes.