SQL Server - Sudden log autogrowth after database backup

Question

I have to manage a SQL Server 2008 Database, using SQL Server Management Studio 2012. The database file is about 290Gb. The problem we have is that the log file used to grow a lot.

Doing some internet research (I'm totally new to DB management), I came to the following conclusions, and applied all of them:

• Set (what I thought to be) a realistic initial log size; 220 Mb.
• Set autogrowth feature to increase in MB (instead of %).
• Schedule regular log backups (currently once every 15 minutes).
• Schedule a full database backup once a day.
• I've scheduled 4 UPDATE tasks every 10 seconds to keep the log growing. These tasks are performed on 21000 records.

This configuration seemed to worked ok. I've been monitoring the behaviour of the log size, and it never needed to autogrow; when the reserved space for the log file was about to get full, the scheduled backup would free it again. After 11 hours, the log size had only increased up to 260MB, which I considered to be Ok (4x10Mb autogrowths in 11 hours).

So I went home, trusting that this behaviour would persist at night. But the following day, I saw that several autogrowths had been performed. The log size increased from 220Mb to 2350 Mb (more than 200 autogrowths!).

I decided not to change anything, and keep monitoring it; the used space for the log file never grew more than the 18%. I increased the autogrowth size from 10 Mb to 50 Mb.

Again, I was pretty confident that this configuration will prevent autogrowths from happening. And again, I was wrong. This morning I've found that a new autogrowth process has been performed. Only once, but it still makes me feel confused.

I've noticed that this last autogrowth operation took place after the full database backup was performed.

Can please anyone explain me if this behaviour is normal? Why the log size keeps stable during all day, and after the database backup is performed, it suddenly grows? And why does it grow, when barely 20% of the reserved space for the log file was being used at peak times, right before the log backup? Any recommendation?

Answer 1

I wanted to clear this in a comment but there is no room to do this, so I post it as the answer.

While the the key point of what happens is right, i.e. the full backup cause the log to grow, this explanation below given by Ste Bov is completely wrong:

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels

Fisrt of all full backup will NOT make "the new point in time that the tLog backups will work from", yes it creates a new recovery point, but the next log backup will start exaclty from the point where the previous log backup was stopped (this point in the log file, last_LSN, is written to the backup and can be seen using RESTORE HEADERONLY, or by querying msdb..backupset)

Second, starting with SQL Server 2005, concurrent log and full backups are possible, you can prove it yourself. What happens instead is that the log backup cannot clear the log when it finishes if there is a full/differential backup running concurrently. That is because diff/full backup need to write out the "active portion"* of the log in order to get a database in consistent stay when restored.

*to be precise, it's not an "active portion", but all the log records from MIN(LSN of last checkpoint, LSN of oldest active transaction) to the LSN at which the data read portion of a backup ends

So the log should be large enough to be able to accomodate all the activity that is logged for the whole duration of the full backup.

More on this here:

Concurrent log and full backups by Randal

More on how much transaction log a full backup includes by Randal

Answer 2

Why the log size keeps stable during all day, and after the database backup is performed, it suddenly grows?

log growth after backup/any time means you are having some transactions which writes to log more

ex:index rebuild

to know all the transactions that are writing to log and to get understanding of what are those you can use below query .Schedule this as sql job and try to dump this table to know more

select txt.text,(database_transaction_log_bytes_reserved+database_transaction_log_bytes_used) as  logspaceusage
 From sys.dm_tran_database_transactions tr
  join  sys.dm_Exec_requests ec
 on ec.transaction_id=tr.transaction_id
 cross apply
 sys.dm_exec_sql_text(ec.sql_handle) txt

you could also extended events to monitor log growth and log that to a file for later analysis

Answer 3

Your transactionlog backup doesn't clear out the transaction log while the full backup is taking place, this is down to how the backups are organised requiring a full backup to then have the transaction log to work from.

When you start a full backup that is the new point in time that the tLog backups will work from, but they cant work until that backup is complete, so during this time the transaction log grows, once the full backup is complete you now have a new point in time and the tLog backups will now be able to actually release the data from the transaction log and it will return to its normal usage levels