1

I'm hosting a SQL Server 2012 database server with about 100 databases. I use the standard maintenance plan to backup all user databases daily and do hourly transaction log backups.

One database, which is about 80 MB, backs up fine at 6 AM but the following transaction log is typically 1-2 GB. I've checked and the transaction log is getting properly truncated on the full backup but it immediately fills up after the backup. I've tried switching to simple recovery, shinking the transaction log file, then re-enabling full recovery. That all works fine and the .log file goes down to 4-5 MB. However, within a day it's already up to 2 GB - most of which seems to happen when I perform the full backup. The log has gotten up to 10 GB even though it gets truncated daily.

The database is only accessed through a single web application which doesn't appear to have anything hitting the database during the time where the transaction log balloons out.

I've attempted to query the transaction log itself but can't see anything obvious.

How can backing up an 80 MB database create a 2 GB transaction log? How can I find out what data is actually in the log and determine where it's coming from?

Improve this question
3
  • From reading this question, I'm not sure you fully understand how transaction logging works - you can back up a database in full recovery mode over and over and the log is not going to magically shrink - please read this article- technet.microsoft.com/en-us/library/2009.02.logging.aspx
    – Scott Hodgin - Retired
    Commented Oct 31, 2016 at 16:21
  • 3
    Do you have any other scheduled maintenance tasks (index rebuilds etc.) going on at that time?
    – MrTCS
    Commented Oct 31, 2016 at 16:24
  • @ScottHodgin I didn't mention anything about the log shrinking. I don't expect it to shrink. Read before responding.
    – Jesse B
    Commented Dec 5, 2016 at 2:06

3 Answers 3

Reset to default
1

The statement "transaction log is getting properly truncated on the full backup but it immediately fills up after the backup" is not how backups operate though. All transaction log truncation is done during the first transaction log backup after the full backup, the truncates are deferred until the full backup is completed.

What happens to transaction log backups during full backups?

If you are seeing a bigger transaction log after a full backup but before the first transaction log backup, that's typically by design. However if you are saying you have almost no activity going on during the full backup which would cause a gig of activity then I would immediately start by looking at your jobs. Often a reindex will cause a lot of logging but it would be odd for that to be the main culprit. Carefully monitor what happens during that period and run the same queries. Feel free to profile what is going on during that time as this could be a compound issue.

Improve this answer
0

Does your standard maintenance plan also rebuild all indexes? This can generate lots of transaction log writes. I would:

  • Download the Ola Hallengren maintenance solution from https://ola.hallengren.com/
  • Use that solution to perform intelligent index maintenance (it only rebuilds indexes based on a fragmentation threshold and not all indexes).
  • Determine your Recovery Point Objective (RPO) and Recovery Time Objective (RTO) for the databases.
  • Use the Ola Hallengren maintenance solution to take both full and log backups to satisfy your RPO and RTO.
  • Taking regular transaction log backups will stop your transaction log from growing out of control.
  • Document and test a disaster recovery plan to ensure you are comfortable in restoring the full and transaction log backups in the event of a disaster in a timely fashion.
Improve this answer
-4

Jesse, I'm not sure whether the backup process created a huge log, anyway in your case we have 2 options.

Take log backups every 1 hr. Or If you don't have any replication then make the db to simple recovery mode.

Improve this answer
2
  • As the OP wrote, they already take hourly backups. Additionally, switching to simple recovery mode does not actually reduce the amount of log generated (even if that was the problem, which it is not). It just runs a CHECKPOINT, which flushes dirty pages and clears the virtual log files that are no longer in use.
    – Randolph West
    Commented Oct 31, 2016 at 18:31
  • You mean : let's reduce the RPO ! Not exactly what the OP is asking for
    – Hybris95
    Commented Jun 9, 2017 at 11:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.