Our EPO 5.0 sql database has grown over a short space of time from 11gb to 79gb. The log is at 145gb.....
We have figured out that the database size was due to eventid 1092, which we have currently disabled in the server settings and purged event id 1092 from the sql database, but the size has stayed the same.
What steps would be best to reduce the size of the database?
thanks in advance.
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 Ser... . 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:
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
The way to configure database maintenance for your environment listed above is the following:
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.
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.
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.
, 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 Serv... ), there are some usefull hints and also a SQL script attached.
Hope this helps,