log shipping restore occassionally failing

bingboo · May 17, 2006 11:38AM

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.

petey · May 17, 2006 12:51PM

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>.

bingboo · May 18, 2006 10:44AM

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.

petey · May 18, 2006 12:27PM

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

bingboo · May 18, 2006 1:12PM

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

petey · May 18, 2006 1:25PM

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 1:26PM

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.

warren2600 · August 21, 2006 11:32AM

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

petey · August 21, 2006 12:00PM

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

warren2600 · August 21, 2006 12:09PM

this option is not turned on for either sql server.

petey · August 21, 2006 12:16PM

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

warren2600 · August 21, 2006 1:09PM

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?

petey · August 22, 2006 1:12PM

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?

log shipping restore occassionally failing

Comments

Product Learning

Community Forums

Events & Friends

Simple Talk