VeeamBR MSSQL: Failed to offload backup Error: Transaction ... was deadlocked on lock resources with another process

[pirx]

Hi,

ticket 04467835.

This is a problem that we have for quite some time now. Every day we have some offload sessions that fail because of a SQL deadlock. The MSSQL server already has 14vCPU's. Most of the time when this happens we can see a short CPU spike in MSSQL Studio. But not always. The MSSQL server is dedicated to Veeam, but it's hosting VeeamBR/VeeamOne/VeeamBEM. We changed a regkey for DatabaseDeadlocks from 5 to 15 to now 60 seconds (will be retried 5 times AFAIK). But still the error occurs. We stopped VeeamOne and VeeamBEM services to rule out their load on DB, no change. We added an additional index too. The deadlock is always about "[dbo].[Backup.Model.OIBs.ShadowOIBMappings]",

Backup.Model.OIBs = 110.000 rows
Backup.Model.OIBs.ShadowOIBMappings = 37.000 rows
Backup.Model.Storages = 153.000 rows

Code: Select all

MERGE [dbo].[Backup.Model.OIBs.ShadowOIBMappings] AS target      USING      (          SELECT shadowOibs.id, [dbo].[FnFindOriginalStorageId](shadowOibs.storage_id) FROM [Backup.Model.OIBs] shadowOibs          JOIN [Backup.Model.Storages] shadowStorages ON shadowStorages.id = shadowOibs.storage_id          WHERE shadowStorages.backup_id = @shadow_backup_id      ) AS source (shadow_oib_id, original_storage_id)      ON (target.shadow_oib_id = source.shadow_oib_id)      WHEN MATCHED AND source.[original_storage_id] IS NOT NULL THEN          UPDATE SET target.[original_storage_id] = source.[original_storage_id]      WHEN NOT MATCHED AND source.[original_storage_id] IS NOT NULL THEN          INSERT (shadow_oib_id, original_storage_id)          VALUES (source.shadow_oib_id, source.[original_storage_id])      WHEN MATCHED AND source.[original_storage_id] IS NULL THEN          DELETE                Proc [Database Id = 6 Object Id = 182916369]                        DELETE  [dbo].[Backup.Model.Storages]              WHERE   id = @id OPTION (MAXDOP 1                Proc [Database Id = 6 Object Id = 1233296049]                                                                                                    

Code: Select all

04.12.2020 05:28:57 :: Failed to offload backup Error: Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 05:28:57 :: Microsoft SQL server hosting the configuration database is currently unavailable. Possible reasons are heavy load, networking issue, server reboot, or hot backup.
Please wait, and try again later.

Error information:
Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  

Code: Select all

04.12.2020 05:31:47 :: Failed to offload backup Error: Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 05:28:57 :: Failed to offload backup Error: Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 05:28:07 :: Failed to offload backup Error: Transaction (Process ID 601) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 05:27:37 :: Failed to offload backup Error: Transaction (Process ID 281) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 05:26:53 :: Failed to offload backup Error: Transaction (Process ID 452) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  

04.12.2020 04:08:45 :: Failed to offload backup Error: Transaction (Process ID 158) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 04:08:15 :: Failed to offload backup Error: Transaction (Process ID 599) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 04:04:56 :: Failed to offload backup Error: Transaction (Process ID 561) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 04:05:33 :: Failed to offload backup Error: Transaction (Process ID 360) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  
04.12.2020 04:04:25 :: Failed to offload backup Error: Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.  

Any ideas? With Veeam support I tried to clean up the DB but it seems there is not much to clean up anymore. 99% of the time the CPU has no significate CPU load, I can hardly accept that we have to add more an more CPU's just to prevent these very short spikes most of the time a deadlock happens.

[PetrM]

Hello,

I would not rely on regular database cleanup, I believe it's better to be focused on the root cause of the problem instead of fixing symptoms by running SQL scripts.

You may request to escalate the support case to a higher support level as it seems that a deeper technical investigation is required.

Thanks!

[pirx]

I talked to support engineer, I'll will escalated the case on Tuesday.

[pirx]

Status: I escalated this a while ago and received an additional SQL index that should also be the solution which is implemented in v11. This did not help. So I now have 2 additional regkeys for timeouts (5x60sec) + new index. I really hope that there will be stable solution, because I see this error on a daily basis depending on timing or load. Overall 90% of our current problems are with offload tasks.

Is there / will there be a detailed reports which problems in v11 are solved so that I can decide if an update is worth the risk?

Edit: I see that this does not exist and is not planned. veeam-backup-replication-f2/big-fix-list-t56614.html

[wishr]

Hi Pirx,

As far as I see from the case, it has been escalated to R&D and there is an ongoing investigation. Please, keep working with our engineers until the issue is fully resolved.

Regarding the bugfix list, the post by Gostev is still actual. We deliver so many bugfixes with each release so that it is literally impossible to list them all.

Thanks

[pirx]

Seems support now provided the SQL index that solves the deadlocks. Fingers crossed...