This Q&A was previously posted on the Stack Exchange Network of websites.
This content is licensed under the Creative Commmons Attribution-Sharealike (CC BY-SA) license. As the author of these answers, this archive serves as a canonical location for permalinks, which will outlive Stack Overflow. See all my answers by looking at the Stack Exchange post category.
Transaction log is growing very fast
Question
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.
asked 2017-04-05 by Yorik
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.
A full explainer with pretty pictures is also available in this blog post.
answered 2017-04-06 by Andy Mallon