What are the challenges you face when working across database platforms? Take the survey
Options

Error 920

ABertrandABertrand Posts: 3
edited January 3, 2008 7:44PM in SQL Backup Previous Versions
I am using 4.6. Converting the system to 5.x is not feasible in the short term.

I continue to receive these errors when a backup has completed:

Error 920: Failed to update backup history table (BACKUPMEDIAFAMILY).
SQL error 1205: Transaction (Process ID 636) was deadlocked on lock | communication buffer resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

I assume this is because I recently updated our backup scripts to have multiple backups running concurrently. Is this scenario not supported?

Unfortunately we can no longer afford the time it takes to process all backups serially. So we need to come up with some other way to process all backups on this server within our acceptable window (which we currently exceed if we run them one after another).

Is there a command-line option or xp parameter to suppress the update of the history table in msdb? In this case we are doing full backups only... no tlog backups, no worries about restore to point in time, etc.

Comments

  • Options
    peteypetey Posts: 2,358 New member
    Concurrent backups are supported. The problem here is because of contention to the msdb backup history tables. No, there isn't a parameter to suppress the updates to the msdb tables.

    Is SQL Backup set up to delete old records from the msdb tables? If so, try disabling that option and see if you still receive the deadlock error. Also, try profiling one backup and let me know how long the queries to update the msdb tables take.

    Thank you.
    Peter Yeoh
    SQL Backup Consultant Developer
    Associate, Yohz Software
    Beyond compression - SQL Backup goodies under the hood, updated for version 8
  • Options
    Hi Petey,

    That option was NOT enabled when I was receiving these errors, but I have since observed from our own custom logs that backups were taking gradually longer over time. I have manually cleaned out the table (I am guessing the problem was on trying to write to the table, which had grown very large, not on trying to delete from the table) and now my backups are running much faster. I won't be able to test concurrent backups again for a few days. Hopefully manually clearing out the history table will resolve the other more pressing problem as well.
Sign In or Register to comment.