7 Replies Latest reply on Apr 30, 2008 2:06 PM by seanmcd

    Large Database with epo 3.6.1

      Hi everyone, we have the following system running: Win2003 Server with SQL 2000, ePO 3.6.1 Patch2 and 2500 nodes. The last months the database grows up to 50 GB and reaches the limit. I've done the following to reduce the size:

      - run the "Purge Event task" and delete all older than 360 days
      - reduce the size of the transaction log with some SQL statements
      - Set the epo database to "simple mode" and run the "Shrink Database" Task with the SQL Enterprise Manager

      After that the database had a size of 40 GB, that was also to large. Because we had a lot of events in the last 12 months (most Buffer Overflow and SCM Appliance blocking threats) i've done the following:

      - i run the "Event Removal" task from the Reporting->Events section of the epo console and deleted all events older the 180 days. The number of events was reduced by nearly 60%!
      - After that i run the "Repair Database Events" Task
      - After that i run again the "Shrink Database" Task from the enterprise manager.

      But the size of the database keeps unchanged and was still 40 GB! So i start again the "Purge Events" task to delete all older than 180 days. This task is runs for 80 hours (over three days!) and nothings happens! So i decided to restart the server and try again. But now the task ist again running for over 80 hours, so what can i do? Any ideas how i can free up some database space? How long may this task be running?

      Thanks for help!
        • 1. Recover Mode = simple
          Try setting your DB maintenance plan to "Recover Mode - Simple". This solved our issue of the ePO DB exceding disk space.

          Details here: KB42359
          McAfee recommended maintenance for the ePolicy Orchestrator 3.x.x SQL Database using Enterprise Manager
          • 2. RE: Large Database with epo 3.6.1
            I had a similar situation and had to run the event removal for two week periods at a time. For example, I would run it for the period from 1/14/07-1/28/07 and after that was complete run it for 1/1/07-1/14/07 and continue doing this until the date when ePo was first setup. If I tried to remove the events over a longer period it would eventually fail.

            I now keep the database size down by removing events older than 60 days at the beginning of each month.
            • 3. RE: Recover Mode = simple

              Thanks for you answer. But as i wrote i've done this already.
              • 4. ePO database Events_spam
                If you are having issues with your ePO databse and the Events_Spam table is very large you can use the following script to clean up the db. This can also be used on any other table that is to large. We used the below script because our db was 35 GB. After running the script and cleaning up Event_Spam it droped down to 10 GB. The script was supplied by Support.

                SET rowcount 10000
                DELETE FROM events_spamex
                WHERE Eventdatetime < '2007-04-18'
                WHILE @@rowcount > 0
                DELETE FROM events_spamex
                WHERE Eventdatetime < '2007-04-18'
                SET rowcount 0

                Where events_spamex replace with the table you need to purge.
                Where Eventdatetime change to the way the date is shown in the table example 02-11-2008.

                You can also use the followng to delete the a table in MS SQL 2000.
                truncate table events_spamexrcpts

                Hope this helps someone.
                Thanks Mike.
                • 5. RE: ePO database Events_spam
                  are you logging all events or just selective?
                  this has a major impact on yuor DB size
                  • 6. RE: ePO database Events_spam
                    No. The events are from WebShield. Recommendation was made not to have WebShield dump reports to ePO. The entries dated back to March 2006. Sfter running the script it decreased the size of the table.

                    • 7. RE: ePO database Events_spam
                      Is there any graceful way to stop the PURGE task once it's in progress? I've had one running for 7+ hours now and I'm getting worried that it's either hung or never going to stop.