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?