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

steverowlandssteverowlands Posts: 4
edited August 18, 2010 9:08AM in SQL Backup Previous Versions
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
· Share on Twitter

Comments

  • peteypetey Posts: 2,358 New member
    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'' '
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steverowlandssteverowlands Posts: 4
    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'?
    · Share on Twitter
  • peteypetey Posts: 2,358 New member
    You don't have to. E = expired, S = successful. SQL Backup won't attempt to copy any of these files any longer.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
    · Share on Twitter
  • steverowlandssteverowlands Posts: 4
    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
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    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 
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    what if i have many log backup files which were copied manually and later on job gets started to with network copy.
    Getting too 
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    what if i have many log backup files which were copied manually and later on job gets started to with network copy.
    Getting too 
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    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 
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    what if i have many log backup files which were copied manually and later on job gets started to with network copy.
    Getting too 
    · Share on Twitter
  • lucianoluciano Posts: 0 New member
    what if i have many log backup files which were copied manually and later on job gets started to with network copy.
    Getting too 
    · Share on Twitter
  • SQL_DudeSQL_Dude Posts: 11 Bronze 1
    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.  
    · Share on Twitter
Sign In or Register to comment.