IO is frozen on database xxx, No user action is required

Recently encountered together about "I/O is frozen on database xxx." No user action is required. However, if I/O is not resumed promptly, you could cancel the backup. " The case.

In the event of a problem, I'm going to execute a very simple SQL statement that takes a very long time to check for no blocking. Under normal circumstances, it should be OK in a few seconds. Check the error log later and find that there are a large number of such messages. And this point, we don't have jobs to back up the database. After the search, I learned the reason why this message occurred:

Refer to the online information, about "I/O is frozen on database xxx." No user action is required, which is described below:

This message was logged in the Error Log whenever any backup service making use of SQL Server Virtual Device Int Erface (VDI) tries to backup of the database (with snapshot)/drive on which the database files reside. Microsoft Backup (Ntbackup.exe), Volume Shadow Copy (VSS), Data Protection Manager (DPM) and third party tools like Symant EC Business Continuance Volume (BCV) is some of the application which cause this message to logged in the SQL Server Erro R Log.

What does these messages mean? Let me explain a example. Suppose Ntbackup.exe is configured to take the backup of D drive. This is the some data files related to few databases on SQL Server. Since The data files are in use by SQL Server, if these files is copied as it's the files in the backup would be inconsis Tent. To ensure the database files is consistent in the drive backup, this application internally issues a backup database [DatabaseName] With SNAPSHOT command against the database. When this command is issued, the I/O for that database is frozen and the backup application are informed to proceed with it s operation. Until the BACKUP with SNAPSHOT command was complete, the I/O for the database is frozen and the I/O is resumed once it comp Letes. The corresponding messages is logged in the SQL Server Error Log.

translate as follows:

When any backup service uses the SQL Server Virtual Appliance Interface (VDI) to attempt to back up the database (with a snapshot) or the disk on which the database file resides, the message is logged in the error log. Micorsoft Backup (Ntbackup.exe), shadow copy (Volume Shadow copy VSS), Data Protection Manager (Database Protection manager DPM) and third-party tools, such as Symantec Symantec Business continuity volumes (Continuance Volume) (BCVS), these are applications that cause such messages to be logged to the SQL Server error log.

So what do these messages mean? Let me use an example to explain. Suppose you configure Ntbackup.exe to back up the D drive. This disk has some database-related data files for SQL Server. Because SQL Server uses those data files, it will appear inconsistent if these files are copied at backup time. To ensure that database files are consistent on disk backup, these applications use the backup database [databasename] with snapshot command to back up the databases. When the command executes, I/O on the database freezes and the backup application is notified to continue. Until the backup with snapshot command finishes, the frozen I/O to the database is restored when the back up command is complete. The corresponding message is also logged to the SQL Server error log.

The back check found that just now we have platespin backup jobs at this time (the database server is VMware, the system administrator uses Platespin to do DR). So it is also the reason that the error log appears for these messages . In addition, about this knowledge point, there are some of the following information for reference, learning.

Case Frozen messages while taking NT Backup for SQL databases

For a description of VDI (VSS), refer to the following link

How It works:sql server–vdi (VSS) Backup Resources

Also on database snapshots (SQL Server) It also has some limitations and performance overhead. As shown below:

References:

http://www.sqldbadiaries.com/2010/11/28/io-is-frozen-on-database-no-user-action-is-required/

https://blogs.msdn.microsoft.com/psssql/2009/03/03/how-it-works-sql-server-vdi-vss-backup-resources/

Https://msdn.microsoft.com/en-us/library/ms175158.aspx

IO is frozen on database xxx, No user action is required