locked
Log Shipping - Log file truncation RRS feed

  • Question

  • Hello,

    I have log shipping setting up on my production going to my DR server with every 15 min of Tlog backup , but after doing some online searches about this I still cannot figure out why the transaction log file of the database does not reduce\truncate? It keeps growing. I made sure there are no active or open transactions. Is there any way to check the transactions open during the tlog backup happend? But still the log file is 70gb and does not truncate after the 15 min log backups..

    I did a lot of searching about this, but could not find a solution, please help me on this.

    Thanks

    AJ

    Tuesday, October 20, 2015 9:05 PM

Answers

All replies

  • Hi,

    What are the results of these statements?

    DBCC SQLPERF(LogSpace)
    GO
    
    select name,recovery_model,log_reuse_wait_descfrom sys.databases
    where name = ''--Put Database name here
    Log File won't release the space back to the disk even though it has free space internally unless you perform a log file shrink.

    Hope this helps

    Thanks

    Bhanu

    Tuesday, October 20, 2015 10:27 PM
  • Hi There,

    Transaction log releases the data when you include the truncate option. If you configure Logshipping with default values your log will not truncate.

    If you want to reduce the transaction log size you might need to switch to simple recovery and than shrink and back to full recovery.

    This will break the log shipping sequence so you need to setup again by restoring the full backup and configure for log shipping.

    thanks

    kumar

    • Proposed as answer by Ice Fan Thursday, October 22, 2015 11:10 AM
    Wednesday, October 21, 2015 12:36 AM
  • Thanks for the response, I dont want to run a shrink which will need to break my log shipping. Where do I need to set so that the log shipping does the truncate and releases the space to disk? Is it in log shipping configuration?
    Wednesday, October 21, 2015 5:46 PM
  • Hi,

    There is no such way to configure as you asked. You can use NOTRUNCATE option to shrink the log file being log shipped without breaking it.

    DBCC SHRINKFILE (  'Log_file_logical_name' , NOTRUNCATE)

    See the following links

    https://indiandotnet.wordpress.com/2010/08/14/how-to-shrink-log-file-when-log-shipping-is-implemented-on-database/

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/060c0fb9-2baf-404b-9e73-f0d331474026/logshipping-shrink?forum=sqldatabasemirroring

    Hope this helps

    Thanks

    Bhanu

    Wednesday, October 21, 2015 6:37 PM