Post reply

Log VLF not being released after backup

  • March 11, 2008 at 4:21 pm

    #120277

    I have several databases for which the log file keeps growing and the VLF's are not being released and are getting into a very large number that is impacting performance. I am running TLOG backups every hour and full backups every night and they are successful. I have even issued a manual CHECKPOINT on the database, ran a LOG backup and then checked the DBCC LOGINFO command the all of the VLF's are still Status of 2.

    As per MS's BOL I have even ran the backup a couple of times but no such luck.

    I do know that a Jr. DBA here ran a KILL command on an errant process (infinite loop) on a few of these DB's and I saw this KB article http://support.microsoft.com/kb/317375 and it talks about the KILL statement possibly leaving the transaction uncommitted becuase the transaction cancellation occurs, but it does not roll back; therefore, SQL Server cannot truncate every transaction that occurs after this because the aborted transaction is still open but I did a DBCC OPENTRAN and there are no open transactions.

    Any thoughts on how to get this back under control or am I just going to have to bite the bullet and take this DB offline and create a new log in order to get the VLF's back to normal?

    Thanks

    SJ

    Accepting all invites

  • March 11, 2008 at 8:42 pm

    #788366

    Did you try

    DBCC SHRINKDATABASE('yourDB')

    ?

  • March 12, 2008 at 8:53 am

    #788614

    Thanks!

    That doesn't work either. As I understand DBCC ShrinkDatabase ('mydb') cannot shrink past the size of the VLF's.

    From BOL "Because a log file can only be shrunk to a virtual log file boundary, shrinking a log file to a size smaller than the size of a virtual log file might not be possible, even if it is not being used." So the problem is that I can't get the VLF's to be released they still have a status of 2 when I run DBCC LOGINFO.

    As I understand when you run a log backup or issue a CHECKPOINT, that is supposed to reset the status on the VLF's that have been written to disk so that they can be reused/recycled in the future.

    I am beginning to think that I will have to detach the database and create a new log to clear this issue up.

    SJ

    Accepting all invites

  • March 12, 2008 at 1:27 pm

    #788773

    Show us the output from DBCC OPENTRAN.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 12, 2008 at 2:04 pm

    #788783

    Was replication set on that DB? If so and it hasn't replicated, it will not "release" that space. It releases transactions that are committed AND replicated (if replication is set up).

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • March 12, 2008 at 8:31 pm

    #788858

    DBCC OPENTRAN shows no open transactions. This database is not part of replication.

    I'm thinking this may go to MS PSS team and see what they think. Otherwise, I know that this Saturday we will be rebooting the server and maybe that will force something.

    Thanks!

    Accepting all invites

  • March 12, 2008 at 11:20 pm

    #788890

    So r u saying that you cant free your log file after running :

    Backup log DBNAME with truncate_only

    DBCC Shrinkfile ('Log')

    You can try restaring your SQL service before you restart machine.

Viewing 7 posts - 1 through 6 (of 6 total)

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