This is the way SQL works. Purging information from a SQL DB will actually cause the DB size to grow because every single item that was deleted is logged in the SQL transaction log. Also even though the information has been removed from the DB the DB files still have the space reserved on the file system.
Here are the high level steps to successfully reduce your ePO DB size:
1- Stop the ePO services
2- Backup the ePO DB
3- Purge the data from the DB if you have not already done this via tasks in the ePO GUI. If you have don't worry you have not broken anything just continue to the next step.
4- Shrink the DB
5- Start the EPO services.
It is not required that you stop the ePO services when baking up/shrinking the DB it will just cause these tasks to complete faster.
Here is a KB that covers how to do these steps in OSQL incase you don't have the SQL management tools installed (KB51873):
Thanks Jeremy, that helped quite a bit.
I am suprised how much crap this thing collects and has to deal with. I'm trying to find a way to make it so ePO doesn't have to bloat the crap out of the DB. I'm obviously a newbie at this, but I have a quad core system with all 4 cores pegged at 80-90% non-stop for 450 systems. This doesn't make any sense to me and the Mcshield.exe sucks up 40% non-stop as well. I'm thinking I have something misconfigured.
Thanks again for your help. Still learning!
If you want to determine why the DB is filling up so quickly you may want to run through KB52116:
By far the most common reason for this is eventIDs 1051 and 1059. These events are generated by VSE extremely frequently and they are not really an indication of any threat. You can see the event description and also filter those events out in the ePO console. This will prevent clients from forwarding the events to ePO so they won't be written to the DB (it will not remove the events that have already been written to the DB). Here is how:
- Logon to the ePO console
- Click Configuration | Server Settings | Event Filtering | Edit
- Locate any event you do not want forwarded to EPO and deselect it
- Click Save
I hope that helps!
O btw for the mcshield thing you may want to ping the VSE community but if its installed on your SQL server it is probably scanning the SQL DB files as they are being updated. Just go into the exclusions and tell it to exclude by file type all .MDF and .LDF files.
Thank you! That is great information.
Jeremy, how do I perform Step 3 from the ePO GUI? Our .MDF is up around 25 gigabytes and we'd like to reduce the size of it. I was handed responsibility for ePO at the first of the year and don't know squat about SQL.
The easiest method would be to just run the script in step 5 of KB51873:
You don't have to use OSQL you can use those same SQL commands in query analyzer if you have the full SQL management tools. Keep in mind you MUST do some of these steps in SQL. You can purge the events through ePO but that will actually result in making the DB larger not smaller. The combination of DB backup/events purge/DB shrink will reduce the DB size (all 3 steps are required, don't skip the backup).
To setup an events purge task in EPO 4.0:
- Logon to ePO
- Click Automation
- Click New Task
- Name the task and click Next
- Select Purge Event Log for the task type
- Specify how far back you wish to purge the event (events older than X days)
- Click Next
- Schedule the task
- Click Next | Save
I hope that helps!