Log file growth

  • I have a replicated database I built back in May. The database is now 13Gb, but the log file is at 31Gb. I have 14Gb left on the drive. I've tried shrinking the file and backing up with truncate only, but it doesn't seem to help. This is a replicated database if that makes any difference. How can I get that log file back down to a reasonable size?

  • Query sys.databases. What's the value of log_reuse_wait_descr for that database?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • reuse is at 6; description reads REPLICATION

    Uh, sorry for the multiple posts, guys. I was getting an error that the post failed. Guess I should have checked to make sure it really did fail.

  • Ok, if the reason is replication then it means that the log reader either isn't running or isn't keeping up with the changes. The log records can't be discarded while they are pending replication (transactional replication only)

    Can you confirm that the log reader is running for this publication and that the replicated changes are getting to the subscriber?

    Can you run this in the DB in question and post the output.

    DBCC OPENTRAN

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Replicated Transaction Information:

    Oldest distributed LSN : (0:0:0)

    Oldest non-distributed LSN : (2181:899:1)

  • That's saying that the transactional replication has never replicated a single transaction.

    Is the replication on this server transactional?

    If it is, are you seeing transactions replicating to the subscriber?

    Is the log reader running?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (7/9/2009)


    That's saying that the transactional replication has never replicated a single transaction.

    Is the replication on this server transactional?

    If it is, are you seeing transactions replicating to the subscriber?

    Is the log reader running?

    If I remember correctly, the replication occurs once a day.

    How can I tell if the log reader is running?

  • doug turner (7/9/2009)


    If I remember correctly, the replication occurs once a day.

    Transactional replication is constant. What kind of replication are you using?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Ok, bear with me here. We're not doing transactional replication. We're actually doing a nightly replication to another database, then extracting data from that database. BUT, just in case, where can I go to see what kind of replication I have going on?

  • Run sp_helppublication in the published database. In the results, the replication frequency will tell you what it is

    Books Online


    replication frequency

    Type of replication frequency:

    0 = Transactional

    1 = Snapshot

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • replication frequency is set to 1

  • In that case, somehow you've got a half-set up transactional publication. I really wish I knew how this happened.

    The fix is fairly simple. Create a transactional replication publication. Add a single table to that publication. It can be any table. Finish the wizard, but don't create the snapshot, there's no need. Once that's completed, delete the publication. (yes, I know it sounds strange. It works)

    Once you've done that, run DBCC OPENTRAN and query sys.databases again.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Holy cr.....!!!! That worked like a charm! After doing all that, I ran the backup with truncate_only, then the dbcc_shrink and it went down to 104K!

    I owe you a beer or lunch or dinner or something!

    Uh, am I set, or will I need to do this little trick again?

  • doug turner (7/9/2009)


    After doing all that, I ran the backup with truncate_only, then the dbcc_shrink and it went down to 104K!

    Now do a full backup, because you've just broken the log chain and will not be able to do log backups until a full backup is taken. Once you've done that, size the log for something reasonable based on the workload and frequency of log backups

    Uh, am I set, or will I need to do this little trick again?

    All set. You shouldn't need this again

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • Thanks a bazillion for all the help!

Viewing 15 posts - 1 through 15 (of 28 total)

You must be logged in to reply to this topic. Login to reply