none
Transaction log truncation

    Question

  • Scenario: 1

    Is it necessary to reconfigure entire Logshipping/Dbmirroring/Alwayon if i truncate transaction log?

    Scenario:2

    How to restore transaction logs in  Logshipping/Dbmirroring/Alwayoif if performed manual backup? 

    Monday, April 21, 2014 12:46 AM

Answers

  • Scenario: 1

    Is it necessary to reconfigure entire Logshipping/Dbmirroring/Alwayon if i truncate transaction log?

    Scenario:2

    How to restore transaction logs in  Logshipping/Dbmirroring/Alwayoif if performed manual backup? 

    If you are using SQL Server 2008 onwards then you can not truncate log with TRUNCATE_ONLY  option, the only option is to set database is in simple recovery then shrink log file then set it back to full recovery, however this is not advisable on Production environment as it would break your log chains and also break your log shipping/db mirroring.

    Instead you need to take log backups often just to avoid your log file growth.

    As per as your second question is concerned, if you take log backup manually then make sure your backup file goes to same path as you configured for log shipping, then your secondary server automatically takes care of this.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Monday, April 21, 2014 4:16 AM
  • Hi,

    The key feature of log shipping is that automatically backup transaction logs throughout the day  and automatically restore them on the standby server. Usually we take regular transaction log backups when in FULL or BULK LOGGED recovery mode in order to clear the transaction log for reuse. In Log shipping environment, even if you truncate transaction log, it is no necessary to reconfigure.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions. However, we could not recommend routinely truncating the transaction log whether they are using database mirroring or not. There is a similar issue about what happen at the mirror Database if the transaction log is truncated.

    http://www.sqlservercentral.com/blogs/robert_davis/2009/03/04/How-do-we-handle-transaction-log-maintenance-for-a-mirrored-database/

    As other post, if you take log backup manually for log shipping, you can restore it on the secondary server automatically via the Agent job.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Tuesday, April 22, 2014 7:05 AM
    Moderator

All replies

  • Scenario: 1

    Is it necessary to reconfigure entire Logshipping/Dbmirroring/Alwayon if i truncate transaction log?

    Scenario:2

    How to restore transaction logs in  Logshipping/Dbmirroring/Alwayoif if performed manual backup? 

    If you are using SQL Server 2008 onwards then you can not truncate log with TRUNCATE_ONLY  option, the only option is to set database is in simple recovery then shrink log file then set it back to full recovery, however this is not advisable on Production environment as it would break your log chains and also break your log shipping/db mirroring.

    Instead you need to take log backups often just to avoid your log file growth.

    As per as your second question is concerned, if you take log backup manually then make sure your backup file goes to same path as you configured for log shipping, then your secondary server automatically takes care of this.


    Please mark solved if I've answered your question, vote for it as helpful to help other users find a solution quicker
    Praveen Dsa | MCITP - Database Administrator 2008 | My Blog | My Page

    Monday, April 21, 2014 4:16 AM
  • Hi,

    The key feature of log shipping is that automatically backup transaction logs throughout the day  and automatically restore them on the standby server. Usually we take regular transaction log backups when in FULL or BULK LOGGED recovery mode in order to clear the transaction log for reuse. In Log shipping environment, even if you truncate transaction log, it is no necessary to reconfigure.

    Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. Redoing is accomplished by sending a stream of active transaction log records to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Beginning in SQL Server 2008, the principal server compresses the stream of transaction log records before sending it to the mirror server. This log compression occurs in all mirroring sessions. However, we could not recommend routinely truncating the transaction log whether they are using database mirroring or not. There is a similar issue about what happen at the mirror Database if the transaction log is truncated.

    http://www.sqlservercentral.com/blogs/robert_davis/2009/03/04/How-do-we-handle-transaction-log-maintenance-for-a-mirrored-database/

    As other post, if you take log backup manually for log shipping, you can restore it on the secondary server automatically via the Agent job.

    Regards,
    Sofiya Li


    Sofiya Li
    TechNet Community Support

    Tuesday, April 22, 2014 7:05 AM
    Moderator