How can we help you today? How can we help you today?

Log files bloated in log shipping configuration

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.
hughthomas
0

Comments

5 comments

Sort by
  • Robert C
    Hi,

    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
    Robert C
    0
  • hughthomas
    I am switching from FULL to BULK-LOGGED recovery type to see it that fixes my problem tonight. I am doing a huge data load on all these databases every night. These databases are basically table copies of legacy data that are deleted and repopulated every night. Then I am using log shipping so these same databases can be available on the reporting server as well.

    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.
    hughthomas
    0
  • Robert C
    Ah, the imports probably explain it then.

    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
    Robert C
    0
  • Robert C
    Just came across this blog article on SqlStuff that you might want to watch out for - basically, if you're marking your database read-only before taking the backup, then SQL Server can't modify the change bitmaps, so you end up backing up the same data repeatedly!
    Robert C
    0
  • hughthomas
    Thanks Robert. I bookmarked the blog for future reference. So far my HDD space seems to be back to the normal incremental creep now that I switched to BULK-LOGGED. In my case, my primary databases are live databases with data entry and input always available, and the secondary databases are all on standby in read-only mode, so that I can restore log files daily. Seems to work well for me.

    Thanks again for the help.
    Hugh
    hughthomas
    0

Add comment

Please sign in to leave a comment.