none
Log Shipping - Custom Schedule? RRS feed

  • Question

  • Hi All, I've been having some trouble with Log Shipping breaking over the weekend when we run Full Backup and an IndexOptimize job.  I'm not sure why it's breaking, but the log files get really large (30+GB) right around the time the IndexOptimize job completes.   Is there an easy way to stop log shipping during a certain time-frame to avoid this type of issue?  For example, I'd like to stop the log shipping process between 10pm Saturday and 5am Sunday.  

    Thanks!

     
    Monday, July 28, 2014 8:42 PM

Answers

  •   Is there an easy way to stop log shipping during a certain time-frame to avoid this type of issue?  For example, I'd like to stop the log shipping process between 10pm Saturday and 5am Sunday.  

    Thanks!

     

    For your question yes there is a way to stop log shipping disable all the jobs involved( copy, backup and restore) but when you will start the log shipping again it will first transfer 40G log so what you are thinking is incorrect.

    Logshipping is not breaking its just copy job is taking a long time to transfer logs from primary to Secondary so other job restore is waiting for more logs which it can apply. Since copy is busy transferring  40G log file restore job wont find a log to restore hence alert will be raised.

    I am sure you are rebuilding index with Maintenance plan and this is the blunder you are doing. You should only rebuild index which has fragmentation >30. You can create your custom script or use Ola Hallengen script

    If you leave the logshipping as it is eventually it will recovery I see this scenario quite often in my environment.

    Use Online index rebuild it produces less log than offline index rebuild I showed it here


    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

    • Proposed as answer by Sofiya LiModerator Tuesday, July 29, 2014 7:57 AM
    • Marked as answer by dkraut Tuesday, July 29, 2014 1:58 PM
    Monday, July 28, 2014 9:46 PM
    Moderator
  • So now I've disabled the LS backup/copy/restore jobs and am planning to do a full/differential restore the DB on the remote end and start over with log shipping.  If you have a better idea, I'm all ears.  btw, I'm not a DBA, I just inherited this mess because we do not have a DBA and no one else wanted this hot potato.  Lucky me!  :)

    You are probably moving correct. But before moving any further

    1. Please run dbcc checkdb on Database present on Primary server to check if there is any data corruption or not. If checkdb comes out clear its not a database corruption. Use below script

    DBCC CHECKDB(db_name) with no_infomsgs, all_errormsgs

    Note that Checkdb does not checks consistency of log files 'completely'. If above comes out clean we cna say DB is is consistent state and then you can proceed as you are doing.

    2. Please find out why this happened, probable cause disk corruption. Look at event viewer and SQL Server errorlogs. Involve your SAN team to check hardware in all possible ways. Mitigating hardware issue with your above steps will mitigate corruption issue completely and would not allow it to pop up later.


    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

    • Marked as answer by dkraut Tuesday, July 29, 2014 1:57 PM
    Tuesday, July 29, 2014 8:49 AM
    Moderator
  • Just to add a point to Shanky's suggestion.

    Online rebuild only works in enterprise edition. If you are using Standard edition of SQL Server then your only option is to use offline and in that case you need to split your maintenance into multiple windows ie different tables on different days. You might want to do tables which are huge and highly fragmented on specific days and small ones on other days.

    I had this problem and based on the size of tlog it creates I segregated the rebuild process, such that the log will not ideally grow over 2-3 gb any time.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by dkraut Tuesday, July 29, 2014 1:57 PM
    Tuesday, July 29, 2014 8:51 AM

All replies

  •   Is there an easy way to stop log shipping during a certain time-frame to avoid this type of issue?  For example, I'd like to stop the log shipping process between 10pm Saturday and 5am Sunday.  

    Thanks!

     

    For your question yes there is a way to stop log shipping disable all the jobs involved( copy, backup and restore) but when you will start the log shipping again it will first transfer 40G log so what you are thinking is incorrect.

    Logshipping is not breaking its just copy job is taking a long time to transfer logs from primary to Secondary so other job restore is waiting for more logs which it can apply. Since copy is busy transferring  40G log file restore job wont find a log to restore hence alert will be raised.

    I am sure you are rebuilding index with Maintenance plan and this is the blunder you are doing. You should only rebuild index which has fragmentation >30. You can create your custom script or use Ola Hallengen script

    If you leave the logshipping as it is eventually it will recovery I see this scenario quite often in my environment.

    Use Online index rebuild it produces less log than offline index rebuild I showed it here


    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

    • Proposed as answer by Sofiya LiModerator Tuesday, July 29, 2014 7:57 AM
    • Marked as answer by dkraut Tuesday, July 29, 2014 1:58 PM
    Monday, July 28, 2014 9:46 PM
    Moderator
  • Thanks Shanky, very interesting observation and thanks for the Index rebuild info! ... I had a similar thing occur last week and as you said, it did eventually recover, but this time I think one of the very large log backup files is corrupt because my event viewer on the remote end is filled with Event ID 3266 and 18210 errors ->

    "The backup data at the end of "\\xxxx_xx\dir123\xxxxxx_20140727120000.trn" is incorrectly formatted. Backup sets on the media might be damaged and unusable. To determine the backup sets on the media, use RESTORE HEADERONLY. To determine the usability of the backup sets, run RESTORE VERIFYONLY. If all of the backup sets are incomplete, reformat the media using BACKUP WITH FORMAT, which destroys all the backup sets."

    So now I've disabled the LS backup/copy/restore jobs and am planning to do a full/differential restore the DB on the remote end and start over with log shipping.  If you have a better idea, I'm all ears.  btw, I'm not a DBA, I just inherited this mess because we do not have a DBA and no one else wanted this hot potato.  Lucky me!  :)

     


    • Edited by dkraut Monday, July 28, 2014 11:26 PM
    Monday, July 28, 2014 11:25 PM
  • So now I've disabled the LS backup/copy/restore jobs and am planning to do a full/differential restore the DB on the remote end and start over with log shipping.  If you have a better idea, I'm all ears.  btw, I'm not a DBA, I just inherited this mess because we do not have a DBA and no one else wanted this hot potato.  Lucky me!  :)

    You are probably moving correct. But before moving any further

    1. Please run dbcc checkdb on Database present on Primary server to check if there is any data corruption or not. If checkdb comes out clear its not a database corruption. Use below script

    DBCC CHECKDB(db_name) with no_infomsgs, all_errormsgs

    Note that Checkdb does not checks consistency of log files 'completely'. If above comes out clean we cna say DB is is consistent state and then you can proceed as you are doing.

    2. Please find out why this happened, probable cause disk corruption. Look at event viewer and SQL Server errorlogs. Involve your SAN team to check hardware in all possible ways. Mitigating hardware issue with your above steps will mitigate corruption issue completely and would not allow it to pop up later.


    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

    • Marked as answer by dkraut Tuesday, July 29, 2014 1:57 PM
    Tuesday, July 29, 2014 8:49 AM
    Moderator
  • Just to add a point to Shanky's suggestion.

    Online rebuild only works in enterprise edition. If you are using Standard edition of SQL Server then your only option is to use offline and in that case you need to split your maintenance into multiple windows ie different tables on different days. You might want to do tables which are huge and highly fragmented on specific days and small ones on other days.

    I had this problem and based on the size of tlog it creates I segregated the rebuild process, such that the log will not ideally grow over 2-3 gb any time.


    Regards, Ashwin Menon My Blog - http:\\sqllearnings.com

    • Marked as answer by dkraut Tuesday, July 29, 2014 1:57 PM
    Tuesday, July 29, 2014 8:51 AM
  • Great information, thanks! 
    Tuesday, July 29, 2014 1:58 PM