The Log Copy Queue

When you create a transaction log backup file using SQL Backup, and request that the file is copied to another location (either using the COPYTO keyword, or with the Copy backup to network option in the SQL Backup wizards), the backup files are placed in the log copy queue before they are copied. SQL Backup checks the log copy queue regularly for transaction log backups that are waiting to be copied (see below for details of the copy schedule). Backup files are then copied in the correct order; log backup files for lower LSNs are copied first.

If there is a problem during the copying process (caused by an extended network outage, for example) and the copy fails, the backup files remain on the log copy queue, ready for another copy attempt.

To revert to the SQL Backup 5 copy behavior for transaction log backups, you must edit the backup script and specify the USESIMPLECOPY keyword in the BACKUP command. You cannot specify the old copy behavior directly from the Log Shipping wizard.

Display

The Log Copy Queue tab shows every transaction log backup file in the log copy queue for the SQL Server instance or database that you have selected in the Registered SQL Servers pane.

To update the details, on the View menu, click  Refresh Connection (or press F5).

The Log Copy Queue tab shows basic information such as the source database, file name, creation date, and copy location for each file on the queue. The Status column indicates the current copy status of each file:

 PendingThe file is due to be copied to the location shown in the Copy Location column.
If a previous attempt to copy was unsuccessful, the Last Attempt to Copy column indicates when the last copy attempt occurred. To show a log of all copy attempts for a file, double-click the item. The Copy attempt log is displayed.
 CopyingThe file is in the process of being copied to the location shown in the Copy Location column. Note that transaction log copy activities are not shown on the In Progress tab.

Interaction

By default, the transaction log backup files are listed in order of creation date, with the most recent first. You can change the sort order by clicking the header for the column by which you want to sort the list. Click the header again to reverse the order. You can also resize the columns as required.

To show a log of all copy attempts for a particular file, double-click the item. The Copy attempt log is displayed.

Log copy schedule

By default, SQL Backup attempts to copy each transaction log backup file as soon as it is added to the log copy queue. If the first copy attempt fails, subsequent copy attempts are made according to the following schedule:

Copy attemptCopy attempt is made...
1Immediately
22 minutes after the 1st copy attempt failed
34 minutes after the 2nd copy attempt failed
46 minutes after the 3rd copy attempt failed
58 minutes after the 4th copy attempt failed
610 minutes after the 5th copy attempt failed
All subsequent copies10 minutes after the preceding copy attempt failed

SQL Backup will continue trying to copy the oldest transaction log backup file every 10 minutes, for up to 24 hours.

You can change the copy schedule by creating and editing registry entries in the following registry key: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

Changes to these registry settings will not take effect until you restart the SQL Backup Agent Service.

Registry entryTypeDescription
COPYTO:CopyIntervalInMinutesDWORDThe initial interval between copy attempts (2 minutes by default).
COPYTO:MaxCopyIntervalCountDWORDThe number of copy attempts for which the interval to the next copy attempt is increased (5 by default).
COPYTO:ExpiryIntervalInMinutesDWORDThe maximum time to continue with copy attempts (1440 minutes by default).
COPYTO:ThreadCountDWORDThe number of files that can be copied simultaneously (5 by default). Maximum value is 10.

The interval between copy attempts after the number of attempts specified by COPYTO:MaxCopyIntervalCount is derived by multiplying COPYTO:CopyIntervalInMinutes with COPYTO:MaxCopyIntervalCount. For example, using the default values, this is 2 x 5 = 10 minutes.


Didn't find what you were looking for?