locked
Log files increasing in Log shipping RRS feed

  • Question

  • Hi All

    I have a disk space problem after configuring Log shipping.

    Log shipping is configured on one of my database for every 15mins. Now the size on the server is getting low. What i should do in this scenario ?

    Shall i shrink the log files or truncate. If i truncate the log files log shipping breaks.


    Navakanth

    Tuesday, March 11, 2014 6:01 AM

Answers

  • Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

    Can we delete the older logs by leaving last 10 log files ???


    Navakanth

    are you talking about log backup drive, if so then you can mention in your log shipping configuration how long you want to retain log files.

    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

    • Marked as answer by nav434 Tuesday, March 11, 2014 12:46 PM
    Tuesday, March 11, 2014 8:34 AM

All replies

  • Hi All

    I have a disk space problem after configuring Log shipping.

    Log shipping is configured on one of my database for every 15mins. Now the size on the server is getting low. What i should do in this scenario ?

    Shall i shrink the log files or truncate. If i truncate the log files log shipping breaks.


    Navakanth

    Is your log shipping works properly, if log backup happens and restores on secondary server then log size should not grow, also analyze the output of below query, it would give you some clue why your log size is growing.

    SELECT log_reuse_wait_desc FROM sys.databases

    also see is there any open transactions in your database

    DBCC OPENTRAN('Database_Name')


    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

    Tuesday, March 11, 2014 6:14 AM
  • Hi Navakanth,

    It is not recommended to truncate the log files as it would break the log chain, This would caused the log shipping to break. You may need to reconfigure the log shipping once again to get it working.

    You can try shrinking the log file using the NOTRUNCATE option.

    for eg., DBCC SHRINKFILE (  ‘Database_log’ , NOTRUNCATE)

    Regards

    Sathish S N

    • Proposed as answer by tirumala86 Thursday, July 30, 2015 7:25 AM
    Tuesday, March 11, 2014 6:58 AM
  • Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

    Can we delete the older logs by leaving last 10 log files ???


    Navakanth

    Tuesday, March 11, 2014 7:58 AM
  • Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

    Can we delete the older logs by leaving last 10 log files ???


    Navakanth

    are you talking about log backup drive, if so then you can mention in your log shipping configuration how long you want to retain log files.

    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

    • Marked as answer by nav434 Tuesday, March 11, 2014 12:46 PM
    Tuesday, March 11, 2014 8:34 AM
  • Log size growing in the sense as every 15mins there is a new log, so size is increasing in that disk.

    Can we delete the older logs by leaving last 10 log files ???


    Navakanth

    Hello,

    You can delete transaction log backups which are applied on secondary.You can use below two views to check status or log backed up and aplied.You can also RK on sql server go to reports...standard reports and see log shipping report pn both servers.

    --on primary
    use msdb
    go
    select * from dbo.log_shipping_monitor_primary
    --On secondary
    use msdb
    go
    select * from dbo.log_shipping_monitor_secondary

    You can shrink log files but i dont think it will be of much help.Problem here is disk space I guess so you might consider deleting files already aplied on primary.Or you can move transaction log files  copied to secondary( but still not restored) to different location just to create space.

    Moreover please also try to find out what operation caused massive log file


    Please mark this reply as the answer or vote as helpful, as appropriate, to make it useful for other readers



    Tuesday, March 11, 2014 8:57 AM