working with huge transaction logs

mliu

We have recently purchased your SQL Backup Pro. One of the questions that I have is as follow:

There will be twice a year that we run an automated process in a very short period of time that will generate a huge transaction log due to the tasks we have to do with the database. How would this effect the log ship if we were to set up a schedule that runs on an hourly basis?

What is the best approach to handle this? What is the best practice? The size of the transaction log could get to a few GBs.

Thanks.

petey

It depends on your needs and situation. I can't really tell what's the best practice, just some points for your consideration.

If speed is critical in the running of the automated process, and is CPU bound, then running transaction log backups during that period would eat up valuable CPU cycles, which in turn would slow down your process.

If the only process creating transactions is the automated process, then turning off log backups during this time may be an option, after making a transaction log backup before the run of the automated process, so that you can revert to the time just before the process.

However, if there are 'other' transactions being created the same time as the automated process, then turning off transaction log backups may not be an option if you want to minimise the risk of data loss. In this case, running a native backup may be an option, since it consumes much fewer CPU cycles compared to SQL Backup.

If using native backups, you would need to restore these backups on the standby server, in the same order as they were created, before any subsequent SQL Backup created backups.

Again, depending on the size of the database and amount of data inserted/updated and distance between servers, you might consider making a transaction log backup (A) after completion of the process, then a full backup (B), followed by regular trx log backups (C, D ...), and use (B) as the new base database for log shipping, instead of having to deal with the large trx log (A).