log shipping restore occassionally failing

• 
  It looks like SQL Backup failed to copy the file to your standby server's folder. Was the required file found in the MOVETO folder on the standby server?
  
  Can you check in SQL Backup's logs to see the reason for the failure to copy? The logs are found in <system drive>:\Documents and Settings\All Users\Application Data\Red Gate\SQL Backup\Log\<instance name>.

  petey May 17, 2006 12:51

  - 0 +
• 
  5/18/2006 9:38:12 AM: Error 915: Failed to update backup history table (BACKUPSET).
  5/18/2006 9:38:12 AM: SQL error 1205: Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  bingboo May 18, 2006 10:44

  - 0 +
• 
  Are you running multiple SQL Backup jobs concurrently, and have also set SQL Backup to delete SQL Server backup history > x days old?

  petey May 18, 2006 12:27

  - 0 +
• 
  yes we have 3 databases that are being shipping. files are set to delete after 7 days.

  bingboo May 18, 2006 13:12

  - 0 +
• 
  I was referring to the 'Delete SQL Server backup history' option in the found on the Options page.
  
  When this option is enabled, the SQL Backup engine will run the sp_delete_backuphistory system stored procedure upon the completion of each backup. However, this stored procedure uses cursors to carry out its work, which is slow if the backup history tables are large, and may also block SQL Backup from updating backup information.
  
  There are various suggestions on how to improve the performance of this stored procedure, one of them being to add a couple of indexes to the msdb..backupset table (note that backupset is a system table).
  
  You could try disabling the deletion option in SQL Backup, and manually run sp_delete_backuphistory manually at set intervals to keep the backup tables to a manageable size.

  petey May 18, 2006 13:25

  - 0 +
• 
  I was referring to the 'Delete SQL Server backup history' option in the found on the Options page.
  
  When this option is enabled, the SQL Backup engine will run the sp_delete_backuphistory system stored procedure upon the completion of each backup. However, this stored procedure uses cursors to carry out its work, which is slow if the backup history tables are large, and may also block SQL Backup from updating backup information.
  
  There are various suggestions on how to improve the performance of this stored procedure, one of them being to add a couple of indexes to the msdb..backupset table (note that backupset is a system table).
  
  You could try disabling the deletion option in SQL Backup, and manually run sp_delete_backuphistory manually at set intervals to keep the backup tables to a manageable size.

  petey May 18, 2006 13:26

  - 0 +
• 
  any updates?
  I have the same issue.
  i'm running 4 clusters using sqlbackup v4.5 to standby severs all v4.5
  
  there are about 40 dbs on each cluster each running to a dedicated standby.
  
  log shipping works wonderfully for about an hour.
  then....exitcode 920, sqlerrorcode 1205 for the backups...deadlocking
  which of course causes 4305 for the restores.
  This is fixed when i place the missing file in the folder....
  not a valid option.
  
  anyone solve this issue?
  
  Thanks,
  Warren

  warren2600 August 21, 2006 11:32

  - 0 +
• 
  Have you tried turning off the 'Delete SQL Server backup history' option found in the GUI Options dialog, for the backup servers?

  petey August 21, 2006 12:00

  - 0 +
• 
  this option is not turned on for either sql server.

  warren2600 August 21, 2006 12:09

  - 0 +
• 
  On each of the servers. are there one or more backup jobs running concurrently to back up the 40+ databases?

  petey August 21, 2006 12:16

  - 0 +
• 
  i run the backup jobs every 15 minutes.
  
  most run simultaneaously....as they should.
  The backup times are not outrageous...about 4 minutes each.
  
  but some of the restores are taking up to 20 - 30 minutes for 15k log file....if they even restore at all.
  
  should i not be using red-gate for so many databases?

  warren2600 August 21, 2006 13:09

  - 0 +
• 
  How large is your backupmediafamily table? Unfortunately, running a few jobs concurrently increases the chances of a deadlock, as one of SQL Backup's update stmt requires a table scan of the backupmediafamily table.
  
  In some cases, creating an index on the physical_device_name field in msdb..backupmediafamily helps to reduce the probability of a deadlock occuring, though the usual warnings about modifying system tables apply.
  
  With regards to long restore times, is this due to the actual log restore, or some post restore tasks e.g. deletion of old log files?

  petey August 22, 2006 13:12

  - 0 +

Add comment

Please sign in to leave a comment.