none
Log shipping(sqlserver 2005) backup job failure RRS feed

  • Question

  • Hi

     

    Im planning to setup sqlserver 2005 log shipping between two servers.The size of the database is around 50GB and we are taking transaction log backup and shipping at 30mins interval.

     

    Suppose if the backup job couldnt take the transaction log backup with in the specified interval ie 30mins,then how does the copy and restore job works. Will it give an error saying tht the transaction log could nt be copied and restored ???

    How to deal with this kind of situations where the backup is taking more time than the threshold time limit and ur copy,restore fails. Is there a way to handle this kind of situation in logshipping Sqlserver 2005.

    and how does it affect the Secondary database. ??

     

    Please help me out !!

     

    Thnkx in advance

     

    Regards

    Arvind L

     

     

     

     

    Friday, February 1, 2008 11:32 AM

Answers


  • Now let us suppose my settting on log shipping screen  'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary  for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???

    It means that if restore operation is not happening till 180 minutes then an alert will be issued to the corresponding recepients. If it is not able to find any file the restore job will fail and it will get reported in the restore job's history. But if the restore job keeps failing continuosly till 180 minutes it will generate and alert..

    If possible, schedule the copy job to occur every 30 minutes or more so that once the T-log completes it will copy the same and  restore it..


    - Deepak

    Monday, February 4, 2008 7:24 AM
    Moderator

All replies

  • In any case ensure the transactions are not that big to commit as it will have same size within Transaction log that will have side-affect on trans.log backu file.

     

    Coming to your doubt yes if it is unable to apply log restore on secondary server it will error out if the current RESTORE job is executing, so better to make sure to keep the transactions in smaller batches and if required you might run transaction log backup job every 15 minutes based on the activity on the database.

     

    Saturday, February 2, 2008 11:47 PM
    Moderator
  •  

    thanks for the suggestion satya.

     

    I cannot schdule the log backup at 15mins bcoz of some business restrictions.They have some network related restrictions here at my office.

     

    I hve one more doubt abt the log shipping concept.As said by you ,'it will error out if the current RESTORE job is executing'. 

    Now let us suppose my settting on log shipping screen  'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary  for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???

    My scenario is :

    My Avg size of my tlog is 500MB and im planning to set it up at 30mins interval.But at some part of the day the Tlog suddenly increases up to 1.5GB.So i wanted to know, wht if that 1.5GB tlog file is unable to copy and restore at 30mins interval?.

     

    Please clear this doubt

     

    Regards

    Arvind L

    Monday, February 4, 2008 6:58 AM

  • Now let us suppose my settting on log shipping screen  'Alert if no restore occurs within' is set to '180mins', then does this setting mean that the restore job will keep on looking for the copied file in the folder on secondary  for next 90mins and if its not able to find any, it will generate an alert after 90mins ??? or it will generate an error if its nt able to find any copied file after the first restore job execution.???

    It means that if restore operation is not happening till 180 minutes then an alert will be issued to the corresponding recepients. If it is not able to find any file the restore job will fail and it will get reported in the restore job's history. But if the restore job keeps failing continuosly till 180 minutes it will generate and alert..

    If possible, schedule the copy job to occur every 30 minutes or more so that once the T-log completes it will copy the same and  restore it..


    - Deepak

    Monday, February 4, 2008 7:24 AM
    Moderator