Epicor and Microsoft SQL Transaction Log Maintenance – Performance Tips and Tricks
Many Epicor 10 ERP installations suffer from a “lack of DBA”. It’s not for lack of desire, but keeping a full-time DBA (Database Administrator) on staff can be expensive. However, a lack of DBA can ultimately wreak havoc in your Epicor environment.
SQL Databases all have transaction logs. The goal of such a log is to keep the data integrity in case of data loss, corruption or other bad things. Think of a transaction where you transfer money from checking to savings. Imagine if the balance in your checking went down, but the savings didn’t increase by the same amount! That would be a huge problem, and SQL transaction logs ensure that doesn’t happen. The entire transaction takes place, or none of it. Another benefit to a transaction log is that it can be used to restore a database to a specific point in time. Imagine if you could rewind the clock and restore your database to the minute BEFORE the user posted all of today’s activity to the wrong GL account, or similar mayhem. Point-in-time recovery can be very helpful, without it you are restoring from last nights full backup, potentially losing a day of data entry.
Backups Stop Running?
If backups stop running (for ANY reason), then SQL transaction logs start filling up. Once a transaction log fills up (or fills the disk it’s stored on), then the database stops, and all your Epicor users stop too. Therefore, monitoring your disk space (see yesterday’s Epicor ERP 10 Server Disk Space report) is critical, but so is knowing about SQL Transaction logs, and what they mean to your business and data recovery.
SQL Databases can be set to SIMPLE recovery mode, where the transaction log doesn’t grow, and can’t be used for point-in-time recovery. This is great for databases where you don’t need recovery – Temp databases, SSRS Temp databases, Epicor Test and Training databases, and others.
SQL Databases can also be set to FULL recovery mode, where the transaction log will grow (and grow) until you do a transaction log backup. Once the backup is complete, SQL knows it can reuse that space, and the transaction log wraps and generally doesn’t grow beyond that. It reaches statis, and that’s good.
About once a month, we get a call from a frantic (future) client who’s Epicor is crashed, and their disk is full, and the SQL transaction logs are HUGE – often larger than the database itself. Our EstesGroup DBA’s jump to the rescue! We generally perform emergency surgery – set the database to simple recovery mode, truncate the logs, set the database to full recovery mode and then do a full backup. Now, before the DBA’s out there jump up and start yelling, I know that those steps can be potentially risky. BUT, if the application is down, and time is money, then getting things back up and running FAST is the name of the game.
After the system is back and running, then we look at why the transaction logs got that big and what can be done about it. It usually involves backing up the transaction log regularly or setting the databases to SIMPLE recovery mode (with the understanding that point in time recovery is no longer an option for those databases).
And, do yourself a favor, don’t backup your transaction logs to the same volume that the logs themselves are stored on! It’s like shooting yourself in the foot – it hurts, and you don’t want to do that right before the holiday!
If you liked reading the “Eighth Day of ECHO” return to our main list to read all of the other “12 Days of ECHO” posts.