1 Reply Latest reply on Oct 20, 2010 12:08 AM by Sailendra Pamidi

    SQL transaction logs issue

      I just stepped into a new position, am now in charge of an EPO 4.5 server.

       

      It was not working well, found that the DB size is at the 4gb limit imposed by SQL Express.

       

      Did a bit of research, looks like it isnt that hard to purge events, shring the DB, and be on my way.

       

      Problem I have now is that during the purge process, the SQL transaction log file gets huge. I started with 10gb free space which wasnt enough

       

      Moved some files, got about 30 gb free total. Running again. The transaction file is sitting at 21gb so far during the process.

       

      Am I doing something wrong? Is there a better way to do this?

       

      When I checked to see what was taking up the most space in the DB, i was event 1095 with about 1.5 million instances.

        • 1. Re: SQL transaction logs issue
          Sailendra Pamidi

          You can try deleting the events in chunks of a few thousand instead of all at once. That way you have some control over the rate at which the transaction log grows.  please check the KB68961 for a script which will help you do this. You may want to modify the script a bit by adding an additional clause to the WHERE condition to filter by a particular eventid like so:

           

          SET rowcount 10000
          DELETE FROM epoEvents
          WHERE detectedutc < '2008-05-21' AND ThreatEventID=1095
          WHILE @@rowcount > 0
          BEGIN
          DELETE FROM epoEvents
          WHERE detectedutc < '2008-05-21' AND ThreatEventID=1095
          END
          SET rowcount 0
          GO


          Ensure that you change the '2008-05-21' to reflect the date that you want everything earlier to be deleted. This deletes the events without filling up the log file if hard drive space is low.