Sign in
 
Home20122008 R2200820052000LibraryForums
SQL Server >
> 
> 
Status "Log_Backup" on DB in AlwaysOn

Answered Status "Log_Backup" on DB in AlwaysOn

  • Tuesday, February 26, 2013 3:12 PM
     
     
    Sign In to Vote
    0

    Hello DB-Administrators

    We have two Windows Server 2008 R2 (x64) with SQL Server 2012 SP1 (BUILD: 7601).

    Multiple Databases running with AlwaysOn in an Availability Group. With the exception of one database it works very fine.

    The problem:
    A very large log file of this database is filling the Log Disk.

    At first, I started a Log Backup. The backup finished successfully, is written in the Log. But I can’t shrink the log file, because of the fact that no unused space is available.

    So I checked the status “log_reuse_wait_desc” of the DB, which is set on “Log_backup”. Next, I checked if there are any open transactions or sessions, but nothing is in progress on that DB at the moment.

    In the internet I found some solutions.

    • One solution in the internet is to run a log backup. (Don't helps at this point.)
    • Set the database recovery model to “simple”, run “checkpoint”, shrink the log file
      • Problem here: AlwaysOn don’t support the recovery model “simple”

    Did I have chance to repair this situation, without excluding the DB from the Availability Group?
    Does anyone know this situation?

    Thanks for any input or ideas.

     

All Replies

  • Tuesday, February 26, 2013 7:39 PM
     
     
    Sign In to Vote
    0
    I think your  best option is still to take log backup  and then be able to shrink. 
    since you already tired regular backup method, 
    let us try this way, 
    run DBCC LOG INFO, do you see value of "2" in the status column. this means that portion of log is active.Do you see it the last or at the beginning?? 
    Status of 0 means that portion of log is not beginning used and so, any log part you see with status '0' is shrinkable.
    However,The way shrink works is it only removes the log from the end. So, If there is '2' in the end of the log files, you shrink will not work .

    For example: Let's say, when you run DBCC LOGINFO, you get like 100 rows( these means your log file has 100 VLF inside)
    if you have status '2' in 100th VLF (the last row), then your will not shrink anything..
    if you have status '2' in 90th VLF (ten rows above the last one), then your log will shrink upto the 10 Vlf's size which might not be very significant space.
    if you have status '2' in the 10th Vlf and all the vlf following it are '0',then you can shrink the pretty much upto the 10th vlf.Of course you have to decide how much log you want, and size it accordingly.
    since you mentioned there are no open transactions. I suggest to run DBCC LOGFINO check where the  active VLF  and take the log backup .

    Hope it Helps!!


    • Edited by Stan210 Tuesday, February 26, 2013 7:41 PM
    •  
     
  • Wednesday, February 27, 2013 12:34 AM
     
     
    Sign In to Vote
    0

    Hi there,

    Try in the following way:

    1. Take the full back up of primary.(in case some thing goes wrong)
    2. Remove this database from the availability group
    3. Take the log backup and shrink the log file on primary
    4. Add back to the availability group.

    Let us know how you go.

    Thanks

    kumar

     
  • Wednesday, February 27, 2013 9:33 AM
     
     
    Sign In to Vote
    0

    Thanks for your Inputs.

    First I tried to run a full backup of the affected DB. Then a error was stopping the run:
    The transaction log for database ‘XYZ’ is full due to 'LOG_BACKUP'. (Microsoft SQL Server, Error: 9002)

    So I shrank other log files on the same disk and ran the proposed command “DBCC LOGINFO”. The result showing in the column “Status” the number “2” in each line.
    Now I started the transaction log backup again. At this time the backup job running over 30 Minutes and created a 40 GB Backup file. So we have 99% of unused space in the log file again.

    The original cause seems to be the missing free space on the disk.

     
  • Sunday, March 03, 2013 12:20 AM
     
     
    Sign In to Vote
    0

    so, you are good now?? when you say, I shrank other log files -- are you referring other database log files...

    I hope you understood the correlation DBCC LOGINFO and "log shrink" operation. also, do not make shrink log as part of your daily activity. plan your log file size appropriately and backup log schedule as per RPO.

    Hope it Helps!!

     
  • Monday, March 11, 2013 5:57 PM
     
     Answered
    Sign In to Vote
    0

    The Disk space was good at this point. We had enough free space that the databases are working properly.

    We don’t shrink Log Files with a scheduled task, just manual if there are any problems with the disk space.

    Then we checked the backup job. Interesting was that no files are created, when I had started a backup job.
    Problem was that our service account of the SQL Server Agent did not have enough permissions.on the server.
    Now our Backup is working properly too.

    Thank you.

    • Marked As Answer by pmuri Monday, March 11, 2013 5:59 PM
    •