6

One of our SQL Server 2014 database transaction logs is growing very fast and I cannot find the root cause for this.

Database in Simple recovery mode. During full backup the log file grows to its limits (800GB) and backup fails.

I checked the transactions during the backup and did not find anything strange.

All transactions are our regular loading and no maintenance jobs (index rebuilds etc.)

I am aware of that backup keep the log from reuse but this mean 800GB changes during the backup period and this does not make any sense.

I had compared this DB to similar DB size, loading and backup duration. The second DB Log is not growing more than 400GB.

DB size is 7TB. Backup time is ~20H. The only time log is growing is during backup. Log_reuse shows correct Backup_Restore. No long running transactions.

The problem is the "speed" of log growth - compared to similar sites with same load and same backup duration, the log is 4 time less.

Improve this question
3
  • @Yorik something must be generating transaction log. You might try to optimize your backup time besides finding the offender (generating tlog). Some tips to reduce backup time.
    – SqlWorldWide
    Commented Apr 6, 2017 at 12:01
  • @SqlWorldWide This exactly the QUESTION how to find the "something" that generate the log. The issue is that I compare it to another environment with the same load but the log file is much smaller. for the backup time, i'm tried do it faster but this will not solve the issue since the log grows so rapidly. Thank you
    – Yorik
    Commented Apr 7, 2017 at 16:07
  • It is with a open transaction that is preventing the log to be truncated, active transaction (you will need to find out what it is), very large VLF (which I doubt, because VLF have default size and cannot be bigger than that). Use the code I pasted here to find open transaction.
    – SqlWorldWide
    Commented Apr 7, 2017 at 18:05

2 Answers 2

Reset to default
3

While taking a full backup of the database, SQL Server is unable to reuse the transaction log for the duration of the backup, even if the database is in SIMPLE recovery. If your database is very busy, or backups take a long period of time, this can require a large transaction log, even if the database is in SIMPLE recovery.

Why does SQL Server need to do this?

Think of the database as a bookshelf containing a set of encyclopedias, and each table is a book in that set. SQL Server is the bibrarian. The librarian needs to make a copy of every book in the set to get a snapshot of the set.

The librarian (SQL Server) starts copying books (tables) at one end of the shelf (database), and makes it's way to the far end. This takes time. If the data within the books is changing (ie, database transactions), getting a consistent (that's the "C" in ACID) snapshot becomes a challenge.

Consider this timeline:

  • The librarian copies book A.
  • The librarian copies book B.
  • Books A & Z are updated.
  • The librarian copies book C.
  • The librarian copies book D.
  • Books C, X, Y, Z are updated.
  • The librarian copies books E-Z. The backup is complete.

The librarian's backup is not consistent. It has some of the old data (Books A & C, and some of the updated data (Books X,Y,Z). In order for the data to be consistent, the librarian could keep track of what changed in books A & C, then roll forward those changes on her backup copy.

It might be OK for the librarian to have an inconsistent backup, but it's not ok for SQL Server to have an inconsistent backup. SQL Server's backups must be consistent to a single point in time.

How does SQL Server do this?

When SQL Server begins backing up data pages, it also starts keeping track of transactions, via the transaction log. After it has backed up the last data page, it then also backs up all of the transactions that occurred during the data backup. Upon restore, it will then roll those transactions forward or backward, as necessary, to ensure a consistent image is restored.

Backing up busy databases in Simple Recovery

Based on OP's comments, the database in question is 7TB, and takes 20 hours to back up. That means that the transaction log for this database must be large enough to accomodate 20 hours worth of transactions. Depending on the amount and type of activity, the amount of transaction log needed could very quite a bit.

To reduce the amount of transaction log needed, you can either try to make your backup go faster, or take your backups during a time of reduced write activity, or both.

Improve this answer
3
  • Thank you for answer The issue is that I know about how and why log used for backup Let me rephrase the question: I have 2 environments with similar loading, same backup time and DB size but log size is 3-4 times bigger on the second one and I try to understand what is going on.
    – Yorik
    Commented Apr 7, 2017 at 16:13
  • Are the two environments under identical load? It sounds like one environment is generating more transaction log than the other. This could be due to more activity, more page splits, different activity that requires more log usage... But ultimately I suspect it is because the two environments have different workloads.
    – AMtwo
    Commented Apr 7, 2017 at 16:19
  • this exactly my question Same load different log size. I will try read transaction log may be i will see something really strange that can explain this behavior. This will be first time that i go so deeply.
    – Yorik
    Commented Apr 8, 2017 at 16:48
1

Based on what you say there probably is a long running ransaction in your database and the log file cannot be truncated untill the tran finishes

You should query sys.databases to find out what is going on. 

SELECT name,
       log_reuse_wait,
       log_reuse_wait_desc
FROM sys.databases;

Columns log_reuse_wait and log_reuse_wait_desc will tell you everything you need to know. Here is the description of what you are going to see there:

  • 0 Nothing
  • 1 Checkpoint Waiting for a checkpoint to occur. This is ok

  • 2 Log backup You are waiting for a log backup to occur.

  • 3 Active backup or restore. Backup or restore operation is running

  • 4 Active transaction There is an active transaction in the database
  • 5 Database mirroring There is a queue on the mirror and it is behind the primary server or mirroring is paused for some reason
  • 6 Replication Replication is running or a database 'thinks' it is marked for replication or everything is ok if you have replication configured
  • 7 Database snapshot creation
  • 8 Log Scan
  • 9 An AlwaysOn Availability

If your problem is long running transaction - your option is to locate it and find out what it is for, for example with the help of Adam Machanic's WhoIsActive stored procedure.

If you deal with database 'thinking' replication is going on, you should probably rebuild your log file.

For more details and possible solutions please refer to the resources mentioned below.

Links:

Improve this answer
1
  • Hi Log reuse is Backup_Restore since backup is running no long running transaction I did compare between this environment and others. the loading is very similar but log growth "speed" different
    – Yorik
    Commented Apr 6, 2017 at 12:00

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.