Log files bloated in log shipping configuration
hughthomas
Posts: 7
I think I'm just making a simple mistake here, but I have a list of databases engaged in log shipping. Every night I perform a full backup before nightly data pulls occur. After the data pull, I do a log backup and restore the logs to a secondary server for reporting.
My problem is the logs keep growing. I thought that when I do a full backup, that the log files would be reduced to the smallest possible size, but this is not happening. I can't do a truncate log or I'll break my log shipping chain.
Any help is appreciated.
My problem is the logs keep growing. I thought that when I do a full backup, that the log files would be reduced to the smallest possible size, but this is not happening. I can't do a truncate log or I'll break my log shipping chain.
Any help is appreciated.
Comments
I wonder if you've got a transaction that's still running somewhere in the background? SQL Server can only truncate the active transaction log back as early as the oldest transaction still running, so if you're seeing your log backups growing constantly, this could be why.
As you say, you shouldn't normally need to truncate the log files - this should generally be reserved for situations where you've had an unusually large burst of activity in a short space of time (like a huge bulk insert operation) that has caused the log files to have to grow beyond what would normally be necessary.
Robert
Red Gate
My main problem is I can't get a handle on what you can and can't do to maintain your log size without breaking the log shipping chain.
It is a mess but I'm getting close. Thanks.
According to this page on TechNet, if you're on BULK-LOGGED, log shipping will transport the changed data pages along with the minimal log records. That should keep your server's live log file down to a reasonable size, though of course the log shipping files will still be large.
Hope that helps,
Robert
Red Gate
Red Gate
Thanks again for the help.
Hugh