We have a SQL Server 2014 SP2 instance, where I discovered a strange behavior. One of the databases (Full recovery model) has a log file of approx 10GB and I see regular file growths at random times.
I haven't yet found out what causes the file growths, but what intrigues me is that the log file is empty in the morning and the log backup (taken every 15min) is most of the time 700KB and one time 12MB.
Can somebody explain how a full log file of 10GB can generate such a small log backups? Can backup compression do that?
Because the log growths happen at night, I wasn't able to execute DBCC SQLPERF(LOGSPACE), but I expect that the file was full, otherwise it wouldn't have grown. Likewise, I cannot say what the log_reuse_wait_desc was.
I have now setup monitoring so I can obtain extra data.
Best Answer
You should check
log_reuse_wait_descofsys.databasesafter 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.
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.
Yes, exactly. You should find this "something" that blocks log truncation.