The Log Copy Queue - SQL Backup

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 to location" setting 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 files that are waiting to be copied (see below for details of the copy schedule). Files are then copied in the correct order; log 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 files remain on the log copy queue, ready for another copy attempt.

Note that to revert to the old (SQL Backup version 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:

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:

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 settings in the following registry folders:

• For 32-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>
• For 64-bit machines: HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\Red Gate\SQL Backup\BackupSettingsGlobal\<instance name>

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

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.

A further registry key, COPYTO:ThreadCount (DWORD) controls the number of transaction log files that can be copied simultaneously. If you want to change the default value of 5, create and edit the registry key. The maximum value for COPYTO:ThreadCount is 10.