You should check log_reuse_wait_desc of sys.databases after taking log backup to find out the reason.
Log backups just try to clear the log, but it can happen that it's impossible.
In this article sys.databases (Transact-SQL) you can find possible reasons WHY the log cannot be cleared, it can be open transaction, replication, mirroring, ecc.
If the log cannot clear it will grow. Once the cause of log retention is gone, log is cleared and can be reused.
One natural cause of this is full backup.
If your db is large enough and full backup takes some hours to complete, even if log backups can run concurrently and their size is small as usual, for the whole duration of full backup the log cannot be cleared.
This means that you log file should be large enough to accomodate all the data modifications for at least these some hours.
Can somebody explain how a full log file of 10GB can generate such a
small log backups?
It's not a log size to generate log records :)
It's your database writing activity.
If you run log backups every 15 minutes and they are about 700Kb it means that your activity generates 700Kb of log records every 15 minutes.
What you are asking about instead is what can it be to prevent my log to be cleared for such a long time that it accumulates 10Gb of data and still cannot be truncated ?
If the log was not truncated at the last log backup, it does not imply that the next log backup will grab more log records. The next log backup will start with the next record to the record where the previous backup was stopped. Log backups are not cumulative.
I will give you an example:
I opened transaction and went home without doing commit or rollback. Log backups run regularly every 15 minutes and every log backup grabs 1Mb of log records. I return to work after 1 week of illness. My transaction is still open! For one week the log could not be truncated. My log backups were still 1Mb, but my log is almost full: it accumulated 1Mb*4 *24*7 Mb=672Mb.
Now that I'm back, I finally issue a commit. The next log backup is still 1Mb but it could finally truncate the log.
So in my case, it could be that something is blocking the log truncation => log file grows but for several weeks a log backup of 700KB can be taken?
Yes, exactly. You should find this "something" that blocks log truncation.
