Database Log Files Growing

brogers

I'm having problems with databases that I backup that are in Full Recovery Mode. The log files continue to grow in size until I shrink them myself and then they start growing again. I have the option on the transaction backup job set to "Remove inactive entries from transaction log". These logs grow to multiple times the size of the databases over time. This is not happening due to large transactions of data as the data in these databases does not change that much. I'm at a loss as to what to do to fix this with SQL Backup. This does not happen to databases that I backup using other tools.

Any assistance or guidance will be appreciated.

Dan B

Hi brogers,

Thanks for posting on the Redgate forums.

I think I will need a bit more detail to get to the bottom of this one.

Can you confirm the backup strategy you have in place? Full/Transaction log/ Differential? Is it the ldf file in SQL which continues to grow or the transaction log backup files you are referring too?

Do all backup jobs in SQL Backup complete without any errors or warnings?

brogers

It is a full backup strategy, with a full backup once a day in the evening and either hourly or every other hour transaction log backups with the "remove inactive entries from the transaction log" checkbox checked. The ldf files are what grow over time. It appears that the log files are continuing to grow instead of reusing available space. If I shrink the files, they go back to a reasonable size and then after some time, they start growing again and continue to grow until I shrink them again after many months. The size of the transaction log backups seem normal. They are a fraction of the size of the ldf files. Everything functions fine and I'm able to do restores from the backups. The only problem is the unneeded use of disk space and the fact that this is not the way transaction logs should be functioning. This is happening to databases on both SQL 2012 and SQL 2014 so I don't think it is a server or version issue. I think the issue is either in SQL Backup or the settings that we are using for SQL Backup.

If it matters we also are encrypting these backups with SQL Backup. I don't know what that would have to do with anything, but it might be something that isn't normally done by others.

Let me know if you need anything else. I know how to make this work with standard TSQL statements, but our primary use of SQL Backups is for the encryption and ease of scheduling. Since we do things through SQL Backups, there is a layer of code that seems to not work.

Thanks

Dan B

Hi brogers,

From my understanding, this would be the correct behaviour. There is a documentation page on this available here http://documentation.red-gate.com/displ ... +backed+up which also links to a relevant Microsoft article. The transaction log would continue to grow until it can wrap around itself in between log backups.

The log backup is truncating the portion of the log that's just been backed up. However, truncating the log simply marks the log entries for reuse - it doesn't imply that the physical log file will shrink. That would be a separate (manual) step. You would over time see the LDF file stabilize at a set size and continue to loop itself around on a daily basis.

please do let me know if you believe this is not the case.

brogers

Dan, I've been out for a few weeks and just saw your post.

All of what you are saying is my understanding of how it is supposed to work. That is not what I am observing however. What I have observed is the log files steadily growing to as much as twice the size of the data files over several months. If these were databases with a couple of gigs of data that would be one thing. These are databases of 200 gig that end up with 400 gig log files. I have observed this happen several times over the last 6 months. Each time I shrink the databases, they start to grow and continue until I shrink them. These are very stable databases that should be consistent in their operational functions. Every time I have checked the available free space in the log files there is very little being used as a percentage of the extremely large log file size. On the order of about 5% used for a 200 gig log file. And this is happening for multiple databases in our environment.

So I'll shrink one of the databases and keep a close eye on it for several weeks to track what happens. I'll update this post if I find more information.