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?
Best Answer
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:
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