While in full recovery mode log % utilization shrinks?

  • Quick question for the group. I’m monitoring log usage using:

    dbcc sqlperf ('logspace')

    The DB is running in full recovery mode. There are no backups running. The application is not rolling any transactions back (this is my performance environment). I would expect the % utilization to only go up as there are no auto grow events but I do see this number drop sometimes?

    Could this be a bug or am I just misinterpreting /misusing this measurement?

  • If you've never done a full database backup since switching to full recovery mode, the log will auto-truncate as if it was in simple recovery mode.

    Fairly simple reason. Without a full db backup to set the base LSN and to base the log backups from, there's no way to recover to point in time, so keeping the inactive portion of the log is useless. Once a full DB backup gets done, the log will no longer auto-truncate.

    If you have done a full DB backup, check that there's no automated job running a backup log truncate or anything like that.

    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 for the info. Yes, thats a very good point. I wasted some time last week on this point. On this occasion I did take a full backup before my test started. Strange.

  • In addition watch out for "autoshrink"/"autoclose" options too.


    * Noel

Viewing 4 posts - 1 through 3 (of 3 total)

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