0

We are on Windows Server 2019 on Azure VM, with SQL Server 2019 Enterprise CU4 installed. SQL Server differential backup jobs are failing intermittently.

I have looked into the SQL Server error log and I can see the following error for all databases:

I/O is frozen on database DBName. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

followed by the following error logs:

I/O was resumed on database DBName. No user action is required.

After this error occurs, the differential backup job starts failing with the following error:

Executing the query "BACKUP DATABASE [DBName] TO DISK = N'X:\MSSQL..." failed with the following error: "Cannot perform a differential backup for database "DBName", because a current database backup does not exist. Perform a full database backup by reissuing BACKUP DATABASE, omitting the WITH DIFFERENTIAL option. BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

At this point we cannot take any further differential backups until we have taken a full backup of the database.

I have checked and there is no other SQL Agent job running at that, and I cannot understand why this error is occurring.

I have done some research online and it appears that it was a known issues in SQL Server version 2016, 2017 and 2019 but it has been fixed in CUs for all those versions. The following article talks about this issue and confirms that issue has been fixed in SQL Server 2019 in CU2.

https://support.microsoft.com/en-us/help/4523102/fix-sql-server-database-remains-in-frozen-i-o-state-indefinitely-when

We have SQL Server 2019 with CU4 applied. So all the fixes should be in there from CU2. However, we are still experiencing the I/O frozen issue.

Any pointers or suggestions are much appreciated. Thanks in advance.

2 Answers 2

1

It sounds like you're using Azure Backups for SQL Server or Azure SQL Managed Backups. This is causing the I/O freeze and resume messages as it uses VSS and the SQL VSS Writer to perform the backups.

This type of backup causes issues with native SQL Differential backups (see here), with the solution being to either stop performing your native backups or stop the Azure backups.

2
  • Hi, I am using Azure Recovery Vault to take daily VM backups, the database backups are taken in the VM and then shipped to an Azure fileshare. I do understand the difference in Azure Managed SQL backups and Backups on the VM it self, this is not the case here.
    – M.Ali
    Commented Jun 19, 2020 at 0:26
  • If you stop and disable the SQL VSS Writer service, the VM backup will stop protecting the SQL databases but will protect the VM as a whole still and your SQL differential backups will start working correctly. There are impacts on your recovery process for this so make sure you understand and are comfortable with this before making the change.
    – HandyD
    Commented Jun 19, 2020 at 0:30
0

Managed to find this article which suggests to make a registry entry to change the behaviour of Azure VM backups and it results in VM Backups not breaking the Differential Backups chain.

The registry entry you have to make is:

[HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\BCDRAGENT] "USEVSSCOPYBACKUP"="TRUE"

Source: https://learn.microsoft.com/en-us/azure/backup/backup-azure-vms-troubleshoot#troubleshoot-vm-snapshot-issues

Article Section: Troubleshoot VM snapshot issues

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.