Error 166

crimdoncrimdon Posts: 54 Bronze 3
edited September 6, 2016 7:51PM in SQL Backup Previous Versions
When I backup a particular database, I get the following error:

Warning 166: Failed to delete old entries in local history tables: OLEDB error running ExecFast command: DELETE FROM backupfiles_copylist_log WHERE copylist_id < 312008 (The lock manager has run out of space for additional locks. This can be caused by
large transactions, by large sort operations, or by operations where SQL Server Compact Edition creates temporary tables. You cannot increase the lock space. []
).

SQL Backup exit code: 166

The backup works fine but it dosen't write the datbase last backed up back to the database.


Regards


Andrew Lackenby

Comments

  • peteypetey Posts: 2,358 New member
    It's probably caused by a SQL Backup data file that's grown too large. Could you please run the following in Management Studio and post the results?

    EXEC master..sqbdata 'SELECT COUNT(*) FROM backuphistory'
    EXEC master..sqbdata 'SELECT COUNT(*) FROM restorehistory'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • crimdoncrimdon Posts: 54 Bronze 3
    Hi there,

    I get 30947 and 19 from those queries
  • peteypetey Posts: 2,358 New member
    You'll need to try deleting the old entries in the backupfiles_copylist_log table in smaller increments manually. Try with 25000 rows each time for a start i.e.

    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 25000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 50000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 75000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 100000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 125000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 150000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 175000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 200000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 225000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 250000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 275000'
    EXEC master..sqbdata 'DELETE FROM backupfiles_copylist_log WHERE copylist_id < 300000'
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
Sign In or Register to comment.