none
SQl 2016 AG question RRS feed

  • Question

  • Hi,

    I have a question regarding the backup of the transaction logs of a Database in a SQL 2016 AG.we have a single 85 gb DB in an AG and we can see that the log files of the DB is getting larger and larger to that reson we must backup the logs.

    What is best way to do this? should we doit on the primary node?

    Thanks


    Shahin

    Friday, May 3, 2019 9:02 PM

Answers

  • HI Cathy,

    Thanks for your reply,

    I already follow the steps but at this step:

    DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

    get this error that make no sense to me!

    Msg 7992, Level 16, State 2, Line 7
    Unable to collapse the read-only database 'MYDB'.

    Thaks


    Shahin

    Hi Shahin,

    AS i mentioned above, in AG writes can only occur on the primary. Shrink operations are writes.The secondary replica is read only, you must shrink database on primary replica.
    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Shahin Friday, May 10, 2019 11:47 AM
    Friday, May 10, 2019 11:41 AM

All replies

  • First of all, you need to take frequent transaction log backups otherwise the log file will grow and use all space and then with no disk space you would face issue with database so start taking it frequently.

    Now you can take log backup either of primary or secondary replica depending on how you have configured secondary replica and what is your backup preference. Please read

    Offload supported backups to secondary replicas of an availability group

    SQL Server on AG, backup preference setting


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Saturday, May 4, 2019 4:07 AM
  • Full recovery model is a prerequisite for adding a database to always on . As we know a database in full recovery model will truncate the log file only on log backup. So in you case log backup is growing since there is no log backups taken. So configure a log backup in either primary or secondary based on your  preference with a backup frequency based on your RPO. 

    And if this is a  non prod environment in which a point in time recovery is not required, for the sake of truncating log file you can try to take backup to NUL device. Refer (http://sqlloggings.blogspot.com/2019/03/taking-log-backup-when-there-is-no-disk.html)


    Thanks, Nithin

    Saturday, May 4, 2019 4:37 AM
  • https://www.edwinmsarmiento.com/when-a-log-backup-does-not-truncate-your-sql-server-log-files-in-an-availability-group/

    /*

    • Your primary replica is at the mercy of the SLOWEST SECONDARY REPLICA

    */


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Sunday, May 5, 2019 4:27 AM
  • Hi Shahin,
    Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.). You can use SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc  FROM sys.databases  to find factors that can delay log truncation. You can get more information from A transaction log grows unexpectedly or becomes full in SQL Server.
    Hope this could help you.
    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, May 6, 2019 7:30 AM
  • Hi,

    Thanks for all your reply,

    question remaing is how can we shrink the curent logs? I have setup a job to backup The logs every 2 hours but as you know this will not shrink the current log. any idea on how to shrink the logs in AG mode?

    Thanks


    Shahin

    Wednesday, May 8, 2019 1:36 PM
  • Hi,

    Thanks for all your reply,

    question remaing is how can we shrink the curent logs? I have setup a job to backup The logs every 2 hours but as you know this will not shrink the current log. any idea on how to shrink the logs in AG mode?

    Thanks


    Shahin

    Shrinking of log i same no matter whether database is simple or participating in AG. YOu can use dbcc shrinkfile command to shrink log file. BUT shrinking is evil process and I would suggest you to avoid it. 

    For DB participating in AG you have logs grow more than the on not participating in AG, also check network strength between the replicas a slow network can cause log file to grow.


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Wednesday, May 8, 2019 2:02 PM
  • Hi,

    Thanks for all your reply,

    question remaing is how can we shrink the curent logs? I have setup a job to backup The logs every 2 hours but as you know this will not shrink the current log. any idea on how to shrink the logs in AG mode?

    Thanks


    Shahin

    Hi Shahin,

    In AG writes can only occur on the primary. Shrink operations are writes. Therefore you must do the shrink on the primary. Could you please try to follow the steps to shrink log file, please refer to Shrinking SQL Log files in an Availability Group Cluster.
    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, May 9, 2019 10:24 AM
  • HI Cathy,

    Thanks for your reply,

    I already follow the steps but at this step:

    DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

    get this error that make no sense to me!

    Msg 7992, Level 16, State 2, Line 7
    Unable to collapse the read-only database 'MYDB'.

    Thaks


    Shahin

    Thursday, May 9, 2019 10:29 AM
  • HI Cathy,

    Thanks for your reply,

    I already follow the steps but at this step:

    DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

    get this error that make no sense to me!

    Msg 7992, Level 16, State 2, Line 7
    Unable to collapse the read-only database 'MYDB'.

    Thaks


    Shahin

    You are trying to shrink read only database you have to shrink database on primary replica

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, May 9, 2019 3:28 PM
  • HI Cathy,

    Thanks for your reply,

    I already follow the steps but at this step:

    DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE);

    get this error that make no sense to me!

    Msg 7992, Level 16, State 2, Line 7
    Unable to collapse the read-only database 'MYDB'.

    Thaks


    Shahin

    Hi Shahin,

    AS i mentioned above, in AG writes can only occur on the primary. Shrink operations are writes.The secondary replica is read only, you must shrink database on primary replica.
    Hope this could help you.

    Best regards,
    Cathy Ji

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Marked as answer by Shahin Friday, May 10, 2019 11:47 AM
    Friday, May 10, 2019 11:41 AM
  • Hi Cathy,

    Finaly I ran the DBCC SHRINKFILE (myDatabaseName_Log, EMPTYFILE); without any error.

    But my logs size didnot change at all.

    I did run: SELECT name, recovery_model_desc, log_reuse_wait,log_reuse_wait_desc  FROM sys.databases 

    And see this:

    master    SIMPLE    0    NOTHING
    tempdb    SIMPLE    0    NOTHING
    model    FULL    0    NOTHING
    msdb    SIMPLE    13    OLDEST_PAGE
    Mydatabase    FULL    2    LOG_BACKUP

    Any idea why the logs dont shrink?

    Thanks


    Shahin

    Monday, May 20, 2019 2:28 PM