Post reply

AlwaysOn transaction log filling up

  • July 18, 2015 at 11:08 am

    #305884

    I have a database that is part of AlwaysOn that is filling up the transaction log drive even though I have a daily full backup and transaction logs set for every 2 hours. The backups are going from both the primary and secondary replica backuping up to the shared disk and I have the backup preferences set to the primary. When I try to shrink the log I get 'The transaction log for database 'DB' is full due to 'LOG_BACKUP''. I have to manually backup the trans log and then shrink, I have no idea why the maintenance plan backups aren't doing this even though they are "working".

  • July 18, 2015 at 11:37 am

    #1813510

    Don't shrink the log. It's not going to help. All you're doing there is forcing the log to grow again.

    What are the settings of the log backup in the maint plan (screenshot please)

    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

  • July 18, 2015 at 1:27 pm

    #1813519

    Need to shrink it or otherwise the devs get errors when inserting etc.

    Backup type: Full / transaction for second plan

    Databases(s): All

    Backup to disk

    Destination: network share

    Checkbox enabled for: For availability databases, ignore priority for backup and backup on primary settings

  • July 18, 2015 at 1:38 pm

    #1813522

    No, you don't need to shrink it. Shrink only removes empty space from the log, so all you've done with the shrink is removed the free space SQL would have reused and force it to grow the log instead.

    Screenshot of the settings of the log backup maintenance plan please. I want to see the settings of the task, not a vague description of the plan

    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

  • July 18, 2015 at 1:46 pm

    #1813523

    There are lots of screens on the backup task, I gave a run down of the important settings, nothing else in there. It's a simple full and trans log backup scheme, I suspect AlwaysOn is somehow causing the issue but not sure.

  • July 18, 2015 at 1:49 pm

    #1813524

    Screen shot of the properties of the log backup task please, the stuff you specified is completely unimportant.

    Availability groups shouldn't cause a LOG_BACKUP wait.

    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

  • July 18, 2015 at 2:24 pm

    #1813526

    https://www.sqlservercentral/Forums/Uploads/Images/1704069-1.png

    Thanks for have a look.

  • July 20, 2015 at 2:54 am

    #1813598

    Ok, so not a copy-only and it looks like they finally took the notruncate option out of maint plans.

    Have you tried increasing the frequency for log backups?

    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

  • July 22, 2015 at 9:35 am

    #1814146

    Regardless if I do a full backup and then a transaction log on either the primary or secondary, the transaction log does not release the space.

  • July 22, 2015 at 9:46 am

    #1814152

    check that none of the secondaries have suspended data movement, open the availability group section and drill down to the secondary database(s). Look for a little pause symbol instead of a green right arrow

    -----------------------------------------------------------------------------------------------------------

    "Ya can't make an omelette without breaking just a few eggs" 😉

  • July 22, 2015 at 9:52 am

    #1814154

    The dashboard shows all green arrows, all dbs are in synchronized state and no data loss.

  • July 22, 2015 at 10:47 am

    #1814160

    Are there any long running transactions? Also, when you manually back up the Log, is that with the DB still in the AG?

  • July 29, 2015 at 10:28 am

    #1815799

    I have had a similar issue with logs in AG.

    Believe it or not, I found that backing up the log three times in a row and then issuing a shrink works for me. You may also want to try backing up the log on the listener as well. I can assure it is possible to shrink the log in AG without removing the DB from the AG and re syncing.

    I understand it's not best practice and I understand that it's odd...but I tried everything and it was the only thing that worked for me.

    USE [pec_prod]

    DBCC SHRINKFILE (N'mcr_dc_new_Log' , 0, TRUNCATEONLY)

    GO

    USE [pec_prod]

    BACKUP LOG [pec_prod] TO DISK = N'nul' WITH NOFORMAT, INIT,

    NAME = N'pec_prod_log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO

    USE [pec_prod]

    DBCC SHRINKFILE (N'mcr_dc_new_Log', 1528)

    GO

    USE [pec_prod]

    BACKUP LOG [pec_prod] TO DISK = N'nul' WITH NOFORMAT, INIT,

    NAME = N'pec_prod_log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO

    USE [pec_prod]

    DBCC SHRINKFILE (N'mcr_dc_new_Log' , 0, TRUNCATEONLY)

    GO

    BACKUP LOG [pec_prod] TO DISK = N'nul' WITH NOFORMAT, INIT,

    NAME = N'pec_prod_log Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10

    GO

    Worth noting this was only on some of my instances. Some instances will backup the log and shrink no problem.

Viewing 13 posts - 1 through 12 (of 12 total)

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