The Log Copy Queue
Published 22 November 2018
When you create a transaction log backup file using SQL Backup Pro, 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 Pro wizards), the backup files are placed in the log copy queue before they are copied. SQL Backup Pro 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.
USESIMPLECOPYkeyword in the
BACKUPcommand. You cannot specify the old copy behavior directly from the Log Shipping wizard.
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:
|Pending||The 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.
|Copying||The 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.|
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 Pro 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 attempt||Copy attempt is made...|
|2||2 minutes after the 1st copy attempt failed|
|3||4 minutes after the 2nd copy attempt failed|
|4||6 minutes after the 3rd copy attempt failed|
|5||8 minutes after the 4th copy attempt failed|
|6||10 minutes after the 5th copy attempt failed|
|All subsequent copies||10 minutes after the preceding copy attempt failed|
SQL Backup Pro 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.
|COPYTO:CopyIntervalInMinutes||DWORD||The initial interval between copy attempts (2 minutes by default).|
|COPYTO:MaxCopyIntervalCount||DWORD||The number of copy attempts for which the interval to the next copy attempt is increased (5 by default).|
|COPYTO:ExpiryIntervalInMinutes||DWORD||The maximum time to continue with copy attempts (1440 minutes by default).|
|COPYTO:ThreadCount||DWORD||The 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.
Creating or editing these registry entries will also affect the behavior of copying backups to hosted storage(Amazon S3 Bucket) (SQL Backup Pro Server components 9.0 and later). For more information, see the In Progress tab.