Sql-server – Transaction log is growing very fast

backupsql serversql server 2014transaction-log

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.

Best Answer

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.

Related Solutions

Sql-server – What ‘resets’ the transaction log

You are backing up the log to the same file repeatedly. Subsequent backups are being appended to that file, which is what you can see in your screen capture. Typically, we tend to backup to dated files:

BACKUP LOG 
    lagerdb 
TO DISK = N'C:\SQLBACKUP\lagerdb-rechts_201112061130.trn' 
WITH NAME = N'lagerdb logs'

Save yourself the trouble of hand cranking the necessary scripts and use Ola Hallengren's maintenance solution.

Sql-server – SQL Server transaction log growing without control

The only way to "solve" it somehow is backing up

Because that is the only way to truncate the log. See How to Shrink the SQL Server log for an explanation on what happens. Normally log backups are scheduled very frequently (think 10 minutes) and they truncate the log. You want a frequent log backup not only because it eliminates the growth problem, but primarily because you want to minimize the amount of transactions lost in case of disaster (crash).

If you want a different behavior, change the recovery model to SIMPLE. In case of failure you will loose all the transactions since your last full backup, but there is no free lunch, is there?