locked
log shipping RRS feed

  • Question

  • Can i shrink the log in log shipping? if it's possible, how can i shrink the log? plz give me reply..
    Saturday, July 10, 2010 3:25 AM

Answers

  • There is no difference in the process in shrinking the log file when you're using log shipping.  The log will be identical on both servers, and any change to the primary (truncation and shrinking) will occur on the secondary.

    If you want to shrink the secondary's log without touching the primary's, then you're out of luck - a log shipped database is identical on both nodes.  

    • Marked as answer by Tom Li - MSFT Friday, July 16, 2010 1:31 AM
    Monday, July 12, 2010 1:01 AM
    Answerer
  • Use DBCC SHRINKFILE command


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 12, 2010 6:16 AM
  • Hi,

    SHRINKFILE may work, however do not forget that Log shipping requires FULL recovery model as it is based on transaction log backups and restores. When you create a log backup the not active piece of the log file will be freed up, but all active transactions will be there. If you have a long running transaction shrinking the file may not help.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Monday, July 12, 2010 8:12 AM
  • Yes, and it before shrinking we can look into DBCC LOGINFO command for transactions that have status =2 (active) at the bottom of the list
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 12, 2010 9:16 AM

All replies

  • There is no difference in the process in shrinking the log file when you're using log shipping.  The log will be identical on both servers, and any change to the primary (truncation and shrinking) will occur on the secondary.

    If you want to shrink the secondary's log without touching the primary's, then you're out of luck - a log shipped database is identical on both nodes.  

    • Marked as answer by Tom Li - MSFT Friday, July 16, 2010 1:31 AM
    Monday, July 12, 2010 1:01 AM
    Answerer
  • Use DBCC SHRINKFILE command


    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 12, 2010 6:16 AM
  • Hi,

    SHRINKFILE may work, however do not forget that Log shipping requires FULL recovery model as it is based on transaction log backups and restores. When you create a log backup the not active piece of the log file will be freed up, but all active transactions will be there. If you have a long running transaction shrinking the file may not help.

    I hope it helps.

    J.


    There are 10 type of people. Those who understand binary and those who do not.
    Monday, July 12, 2010 8:12 AM
  • Yes, and it before shrinking we can look into DBCC LOGINFO command for transactions that have status =2 (active) at the bottom of the list
    Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/
    Monday, July 12, 2010 9:16 AM