Search code examples
sql-serversql-server-2019

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

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.

Solution

  • 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