Options
Error 920
ABertrand
Posts: 3
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.
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
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.
SQL Backup Consultant Developer
Associate, Yohz Software
Beyond compression - SQL Backup goodies under the hood, updated for version 8
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.