log shipping restore occassionally failing

bingboobingboo Posts: 4
edited August 22, 2006 1:12PM in SQL Backup Previous Versions
Looks like I have the same problem as:
http://www.red-gate.com/messageboard/vi ... ction+logs

The jobs will fail because it is missing the very next log.
If I go back and copy/paste the log from backup directory to access directory the job will start back up again. This only happens once in awhile - i do not have it set to delete file on start.
Thx.

Comments

  • peteypetey Redgate › Posts: 2,358 New member
    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>.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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.
  • peteypetey Redgate › Posts: 2,358 New member
    Are you running multiple SQL Backup jobs concurrently, and have also set SQL Backup to delete SQL Server backup history > x days old?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • yes we have 3 databases that are being shipping. files are set to delete after 7 days.
  • peteypetey Redgate › Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • peteypetey Redgate › Posts: 2,358 New member
    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.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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
  • peteypetey Redgate › Posts: 2,358 New member
    Have you tried turning off the 'Delete SQL Server backup history' option found in the GUI Options dialog, for the backup servers?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • this option is not turned on for either sql server.
  • peteypetey Redgate › Posts: 2,358 New member
    On each of the servers. are there one or more backup jobs running concurrently to back up the 40+ databases?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • 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?
  • peteypetey Redgate › Posts: 2,358 New member
    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?
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.