Log Copy Queue is stuck in SQL Backup

When using SQL Backup Log Shipping, you might encounter issues where the log files get stuck when attempting to copy over to a different location. It is possible to troubleshoot this by resetting the Log Copy Queue and then re-attempting the job.
Below are the two queries for clearing the Log Copy Queue. Both of these queries should be ran against the master database of the SQL Server Instance from which the logs originates.
This first query will clear all the entries in the log copy queue which have not been successfully copied. Once a file has been copied the entry is updated with a status of S, so this deletes all entries that do not have a status of S. It is important to note that the S requires two single quotes to work and not double quotes.
exec master..sqbdata 'DELETE FROM backupfiles_copylist WHERE status <> ''S'''
This second query will simply delete everything no matter the status. You should only use this query if the first one does not appear to work.
exec master..sqbdata 'DELETE FROM backupfiles_copylist'
If you still encounter issues after trying the above, you can try using the USESIMPLECOPY keyword. This bypasses the log copy queue and will copy the log backup file in the same manner as the full backups. You will need to add this keyword to the scripts of the log shipping jobs with issue on the CopyTo and you can find that on the SQL Server with the SQL Backup Server Components.
Here is an example of how you would add USESIMPLECOPY to the job.
Was this article helpful?

4 out of 7 found this helpful
Have more questions? Submit a request