The Sage Estimating Management Console has some helpful tools:
- Optimize - defragments/rebuilds indexes and updates statistics
- Cleanup - is an operation that can reduce cruft.
Background information gathered by Greg Bloom with information from Steve Lamb at Sage
In previous threads you mentioned that the Estimating Management Console > Tools > Optimize feature performs the following:
- Updates indexes
- Updates statistics
- Trims log files
Is there anything else that it does?
WRT the log file, it's my understanding that SE is using the 'simple' recovery model. Based on this there really should be any data to remove, correct? (ref. Recovery Models (SQL Server)). Is there something else that you're accounting for with the log file management?
Response from Stephen Lamb at Sage:
Optimize only defragments/rebuilds indexes and updates statistics. It does not trim log files.
SE defaults to the simple recovery model, but nothing is done to prevent customers from changing that and some have.
I agree that trimming the log file should not do ‘anything’ but we’ve run into problems where the lack of trimming has led to out of memory issues. This happened at the end of an upgrade (SE always gets rid of invalid data at the end) for a customer that was using the SDK to create staged report data. The problem was that they never actually deleted the staged data because of a bug in their code. They had as many staged report estimates as they did normal estimates. So, when they tried to upgrade, it would die of memory exhaustion due to log file growth. They had set their database to the full recovery model but even when I tested with the simple recovery model, I had the same issue. SQL Server just doesn’t mark unused data in the log fast enough to keep up with the new deletes (or at least, that’s what I think is going on). Their DBAs cleaned up things themselves.
Another operation that can reduce cruft is to run the Cleanup operation in the EMC. This will delete invalid databases and invalid estimates and staged report data, and if the database is in simple recovery mode, will trim the log file as it goes (it must in order to avoid large log file growth and memory exhaustion).