log backups not clearing transaction log - no open transactions found

  • I have a database in full recovery mode.

    I normally take log backups every two hours.

    However, recently, I have a log that will not clear when running a log backup.

    I have checked for open transactions with

    dbcc opentran() and have none.

    I have checked to see if anything is causing this by lookiing at

    select name, log_reuse_wait_desc

    from sys.databases

    and all databases show NOTHING in the reuse column.

    I have run checkpoint in the database about 5 times and tried rerunning the t-log backup

    but dbcc sqlperf(logspace) is still showing 9% used (about 2 GB out of 20 GB file)

    Any ideas on how to clear the log back to a reasonable 1-2% usage?

  • replication enabled? If not, I might suspect that you have a large VLF that's active. The log is divided up, and depending on how it was sized and grown, you might have VLFs that are large or small. You must have one active, and if it's large, then you can't clear that VLF until it's filled and the next one becomes active.

    Why do you want to get to 1-2% usage? Is there something wrong with 9% usage? I might worry if it were up at 80% usage and I couldn't clear it, but not at that level.

  • I'v been doing some checking.

    No mirroring or replication going on.

    I do have 1400 VLF's

    The reason I want it small is that I am reindexing some large tables and am taking a log backup after each one which typically clears it out. The last one did not clear it out.

  • I must have been close to the VLF boundry.

    The log finally shrunk after some more activity.

    Got to love that DBCC LOGINFO command

  • LOL, glad it worked. Note, that you shouldn't be shrinking the log regularly. If you need that space for index rebuilds, leave the log where it maxes and let is remain there.

    If you have a lot of VLFs, what I'd do is shrink the log down the a minimal size, then grow it in increments. The forumla for VLFs and some recommendations are here: http://www.sqlskills.com/BLOGS/KIMBERLY/post/Transaction-Log-VLFs-too-many-or-too-few.aspx

  • I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running. I've run a maintenance job to see if that would clear out my LDF file but nothing's changing. It's still full...

    Server: Microsoft SQL Server 2005 - 9.00.5057.00 (Intel X86) Mar 25 2011 13:50:04 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • MyDoggieJessie (1/2/2012)


    I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running.

    Please post new questions in a new thread. Thank you.

    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 (1/2/2012)


    MyDoggieJessie (1/2/2012)


    I've got a log reuse type of LOG_BACKUP, but there's no transaction log backup running.

    Please post new questions in a new thread. Thank you.

    I agree with Gail that this should be posted in a new thread, but based on what you posted it soulds like your database is using the bulk_logged or full recovery model and you have no transaction log backups running against the database. The only way to clear the t-log is to run transaction log backups or switch your database to the simple recovery model.

    For more help, please start another thread.

  • I agree with Gail that this should be posted in a new thread, but based on what you posted it soulds like your database is using the bulk_logged or full recovery model and you have no transaction log backups running against the database. The only way to clear the t-log is to run transaction log backups or switch your database to the simple recovery model.

    Sorry, didn't think a new thread was needed - didn't see the point of creating a new post for the same type of question... but, will definitely do should this occur again.

    Lynn, as an FYI the DB is in FULL mode with TLog backups running every 15 minutes (successfully) - I was just confused that the log_reuse_wait_desc

    still said LOG_BACKUP, even when nothing was running...

    I'll open a new thread

    ______________________________________________________________________________Never argue with an idiot; Theyll drag you down to their level and beat you with experience

  • Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.

  • hkamal.infoedge (1/3/2012)


    Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.

    No, no, no, no, no!

    And on a day where the headline article was 'Managing Transaction Logs.'

    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
  • MyDoggieJessie (1/3/2012)


    I was just confused that the log_reuse_wait_desc still said LOG_BACKUP, even when nothing was running...

    A log backup wait reason means a log backup is needed, not that one is 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 (1/3/2012)


    hkamal.infoedge (1/3/2012)


    Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.

    No, no, no, no, no!

    And on a day where the headline article was 'Managing Transaction Logs.'

    Well duh! What else did you expect :-D.

  • Ninja's_RGR'us (1/3/2012)


    GilaMonster (1/3/2012)


    hkamal.infoedge (1/3/2012)


    Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.

    No, no, no, no, no!

    And on a day where the headline article was 'Managing Transaction Logs.'

    Well duh! What else did you expect :-D.

    I'm rapidly reaching the point of 'why the hell do I bother'

    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 (1/3/2012)


    Ninja's_RGR'us (1/3/2012)


    GilaMonster (1/3/2012)


    hkamal.infoedge (1/3/2012)


    Switch the full recovery mode to simple recovery mode and then switch back to full mode and take the full back up.

    No, no, no, no, no!

    And on a day where the headline article was 'Managing Transaction Logs.'

    Well duh! What else did you expect :-D.

    I'm rapidly reaching the point of 'why the hell do I bother'

    because. (nothing else to say!)

    When's the revised version coming out?

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

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