log shipping restore occassionally failing
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.
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
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>.
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: 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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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?
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?
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8