BACKUP LOG Write Fail to Azure Storage

Question

BACKUP LOG Write Fail to Azure Storage

sidney l 21

Hello,

We have a SQL VM running out of Azure with SQL Agent jobs running BACKUP LOG every x hours to Azure Blob. These jobs have been running fine for a long time until recently. We keep getting the below error and these jobs fail intermittently for various databases. They don't always fail at their scheduled run. If it fails, I can re-run the job manually like two minutes later without any issue. I have looked up online and haven't found a solution to my problem.

Does anyone know why and how I can fix this? Any help is appreciated.

Message Executed as user: DSN\xyz. Write on "https://xyz.blob.core.windows.net/CW_Data_LOG_06102022_140000.TRN" failed: 13(The data is invalid.) [SQLSTATE 42000] (Error 3202) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013).
The step failed.

2 answers

Your answer

Answer 1

YufeiShao-msft 7,146

Hi @sidney l ，

From your shown error, it seems like the file may be corrupted, please try to restore to a different server to confirm the drive or only the file is corrupted, or unfortunately both
https://dba.stackexchange.com/questions/98153/error-3203-restoring-sql-server-backup-the-data-is-invalid

If there is a backup, replace the file, if not, you may need another latest backup file

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

sidney l 21 Reputation points

2022-06-14T12:35:02.64+00:00

Hi @YufeiShao-msft

Thanks for your reply.

My issue isn't during the RESTORE process. The error comes up during the BACKUP LOG process to Azure Blob. As I stated in my original post,
I can successfully re-run the failed job manually immediately upon failure.

Could this be something else other than a 'corrupt file' you described?
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2022-06-14T21:19:59.423+00:00

Judging from Yufei's answer, I am not sure that Yufei understands Azure Blob Storage. "Different drive" is a funny suggestion in this context.

You may want to open a ticket with Azure support. Although, I would not be surprised if the answer is that transient errors like this can be expected in the cloud. There is after all some networking involved.

Note that the error is an OS error, and SQL Server is only the victim.

No, I don't think there is anything corrupted here.
sidney l 21 Reputation points

2022-06-14T21:28:36.707+00:00

How could you tell it was caused by the OS and SQL was the 'victim'?
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2022-06-14T21:35:01.62+00:00
Because of:

13(The data is invalid.)

That style a number, followed by a text in parens is an OS error. Well, exactly what it is depends on the error message, for for BACKUP/RESTORE error, this certainly is the case.

You can also try this in a command-line window:

NET HELPMSG 13

And you will see The data is invalid.

As for the exact meaning of this error message, I need to pass. I don't think I've ever seen it before.
sidney l 21 Reputation points

2022-06-14T21:51:05.617+00:00

Also which machine are you referring to hosting the OS?

I did open a ticket with Azure Tech support a few days ago and still waiting for their diagnosis. Thought I posted here as well in case someone has seen it and knows how to fix it.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2022-06-14T21:53:23.837+00:00

The machine where SQL Server is running.

Do I understand your original post correct, that this is a VM in Azure?
sidney l 21 Reputation points

2022-06-14T21:55:23.293+00:00

Yes, an Azure SQL VM. Both OS and SQL running the 2019 server edition. The VM was built using the Azure Gallery Image when we setup/configureed this machine in preparation for on-prem migration.
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2022-06-14T21:58:19.77+00:00

I should clarify that I am not suggesting that there is a bug in the OS. Rather the error is due to some issue when communicating with Azure Blob Storage. Exactly what might be, is way beyond my knowledge.

Whence my suggestion to open a ticket with Azure support.
sidney l 21 Reputation points

2022-06-14T23:14:20.287+00:00

Hi @Erland Sommarskog

No worries. At this point, I'm just looking for any kind of help/suggestion I can get. Thanks for pointing out
that "13(The data is invalid.)" this is related to OS. Honestly I didn't even notice this. Those SQLSTATE error # in caps caught my attention and neglected to notice the 13 and you're absolutely right that it is an OS error based on the format style. You also proved by running the cmd line.

My guess is this is probably more on the Azure Storage side since the action verb "Write" is part of the error followed by the Azure account path. Maybe a bad 'spot' on the blob? Don't know ...
Erland Sommarskog 124.2K Reputation points MVP Volunteer Moderator

2022-06-15T21:13:10.713+00:00

My guess is this is probably more on the Azure Storage side

Yes, I would lean towards that as well. That or the way to the storage.

Answer 2

I realize this post is quite dated now, but I was running into this issue and found the fix for what I was seeing.
We've been writing log backups to BLOB storage for some time, then our backup jobs started failing with the same error as the OP. After hours of troubleshooting, the fix ended up being the removal of a retention rule we put on the storage container.

It turns out that setting up a retention rule via the specific Blob Container page in Azure > Access Policy > Immutable Blob Storage, can block much of the user functionality within the Blob Container. I removed the rule we had in place and the issue went away.

I was able to put the retention rule in another place. From the Storage Account screen in Azure > Data Management > Lifecycle Management > Create rule w/ filter > Add the container name + / to the filter. example: container named 'log-backup' would have a filer of 'log-backup/' so the rule only applies to that specific container and not all of the containers within the storage account.

Hopefully this helps someone else out who had the same issues I did.

Share via

BACKUP LOG Write Fail to Azure Storage

2 answers

Your answer