SQL Server nightly job log management strategy

Question

I have a nightly job that does a bunch of inserts. Since I have a full recovery model, this increases my transaction log size.

Currently I have my log file big enough to accommodate these transactions, but the issue is that the transaction log is mostly empty throughout the day.

Is it an issue (besides disk space) to have a huge (mostly empty) transaction log?

I'm thinking about switching the database to simple recovery before the job, running the job and then switching it back to full recovery. I can have the transaction logs just not be backed up until our nightly differential backup comes around and then i can start the transaction log backup again.

Suggestions?

Answer 1

I'd do nothing. Or have SIMPLE permanently.

Changing the recovery model back to full will require a full backup anyway to preserve integrity later on. You'll have a gap in your LSN chains otherwise.

You've mentioned differential backup, so I assume your full is not each night.

So, putting this together means you'll use more disk space for your full backup than for the LDF file.

Answer 2

I'd keep it FULL and, if push comes to shove, force log backups during the nightly job to keep the log size small. Also, is OK to shrink the log if you really have to, ie. it grew once due to a one time operation and it won't grow back. The fragmentation concerns are more valid for data files, log files have a totally different structure and allocation pattern. Just don't get into the habit of constant shrinking.

I'm saying this because if you do have a backup restore strategy in place already it seems silly to me to increase the window of data loss from 'last log backup' to 'last differential backup'. We're talking a change from 10-30 minutes (typical log backup frequency) to 3-24 hours (typical differential frequency). You won't be able to do differentials as often as log backups because differentials are growing in size (starting with second differential after full each differential is at least as big as previous differential). Log backups only backup the log since last backup, so they stay relative constant in size. Also with a SIMPLE mode you won't be able to do a log tail backup attempt and recover all data in case of crash.

It just seems you're trading off a lot more that you gain with reducing the log file size.