In general, caution is advised when reducing the size of SQL Server database files. The reason is that shrinking the data files will introduce fragmentation in the page structures that SQL Server uses to store data. However, if this was the only time the database reached that size and is not likely to grow to that size again, then it should be fine to do a one-time shrink of the database and the transaction log using SQL Server Management Studio. After doing the shrink operation it is important to rebuild all indexes. More information and background is available here: McAfee KnowledgeBase - Recommended maintenance plan for ePolicy Orchestrator databases using SQL Server Management Studi… . Note that the KB article has a SQL script at the end that you can use to defragment the indexes on a regular basis.
Some other items that may be helpful:
- If you are using ePO 5.0 it is important to upgrade to the latest version.
- There is an ePO extension named Performance Optimizer that will provide further guidance and refinements for managing the ePO database as well as other components. This extension is supported on ePO 5.1.1 or higher.
Thanks for the suggestions. We have completed a one time shrink on the sql db, followed by the SQL script which has bought the database back to a similar size to before. The log is still pretty big at 20gb.
We are on epo 5.3.2 and have performance optimiser installed, just need the time to review the areas that are cause for concern
That's great Kerry, if you have any further questions or ideas about Performance Optimizer don't hesitate to ask.
We've had a similar issue which is really causing our sql database issues, our db has gone up to 38GB which is ridiculous, the dbo.orionsnapshot seems to be the culprit and I can't find any documentation to remove it. As we have a virtual epo server we snapshot it with there. Having used epo for nearly 10 years we've never had the database so big, usually it's about 12GB.
Does anyone know how to remove this or is it safe just to delete the table contents.
Hope you can help.
what i can do if my configuration is one win2008r2 server running ePO 5.1.3 and sql2008r2sp1 EXPRESS ?
is there an "easy way" to perform scheduled maintenence ?
The way to configure database maintenance for your environment listed above is the following:
- Download the SQL Server Management Studio from Microsoft and install it on the server.
- Use this KB article to configure the maintenance tasks: McAfee Corporate KB - Recommended maintenance plan for ePolicy Orchestrator databases using SQL Server Management Studio…
Keep in mind that SQL Server Express is limited to only 10GB database size, 1GB of memory, and will use only one CPU. You may need to eventually upgrade to SQL Server Standard Edition.
Adam on mssql express with SQL Server Management Studio from Microsoft i can't find Maintenance Plans and Maintenance Plan Wizard.
this is made by design by Microsoft. Maintenance Plans is only available in the full SQL Server version. With SQL express you have to script this.
first of all, in this case you should always take a look a the tables which need so much space. Based on this information you know why your database is rising. From my experience most time this is caused by endpoint configuration where a massive amount of events is generated.
Therefore, first of all, take a look at the events and do the appropriate changes in the policy to stop the events.
kerry.manley, you did right to change the settings. Event ID 1092 is stored in the Events Table. After removing the events you have two things to know. The DB files do not downsize automatically. So the DB files are there but most of the storage is free and not used. Also, the Index is very huge and, i think, completely fragmented.
Take a look here (McAfee Corporate KB - Recommended maintenance plan for ePolicy Orchestrator databases using SQL Server Management Studio… ), there are some usefull hints and also a SQL script attached.
Removing the entries for the EPO Server snapshot table:EPO: Empty the EPO SNAPSHOT Table. There is also a script attached.
Hope this helps,