How do I cancel pending jobs in the Log Copy Queue?

steverowlands

Our off-line storage device has failed so the "copy backup to network" has been failing.

I have amended the jobs so they will no longer attempt to do this but I also need to clear down the queue of pending copies. I can't see an obvious way to do this.

Any help would be appreciated.

Steve

petey

Try the following:

EXEC master..sqbdata 'UPDATE backupfiles_copylist SET status = ''C'' WHERE ID = ...'

You can find the IDs using the following to identify the pending tasks:

EXEC master..sqbdata 'SELECT * FROM backupfiles_copylist WHERE status = ''P'' '

steverowlands

Thanks for the reply.

I ran EXEC master..sqbdata 'SELECT * FROM backupfiles_copylist WHERE status = ''P'' ' and it returned no rows.

So I ran:
EXEC master..sqbdata 'SELECT status, count(*) FROM backupfiles_copylist group by status '

and got:

Status
E 1346
S 7996

Is it safe to set all these to status 'C'?

petey

You don't have to. E = expired, S = successful. SQL Backup won't attempt to copy any of these files any longer.

steverowlands

Apologies, yes they have all gone from the list, looks like the last email alert we got was at 10:21 this morning which was about an hour before I checked the database.

Thanks for the assistance with this.

Steve

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too many mail alerts file already exists.. and i want clear all the files with in the range of IDs 

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too 

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too 

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too many mail alerts file already exists.. and i want clear all the files with in the range of IDs 

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too 

luciano

what if i have many log backup files which were copied manually and later on job gets started to with network copy.
Getting too 

SQL_Dude

Changing to C didn't seem to work.  I'm finally getting e-mail messages that the 24 hour time limit has expired but I will probably be getting these for the next couple of hours.