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

最近遇到一起关于"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."的案例。

出现问题的时候，我去执行一个非常简单的SQL语句，执行时间非常长，检查没有阻塞。正常情况下，应该是几秒就OK。后面检查错误日志，发现有大量这类消息.而这个点，我们没有备份数据库的作业。后面搜索，了解了一下这个消息出现的原因：

 

 

参考网上资料，关于“I/O is frozen on database xxx. No user action is required”的介绍如下：

 

This message is logged in the Error Log whenever any backup service making use of SQL Server Virtual Device Interface (VDI) tries to backup 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 Symantec Business Continuance Volume (BCV) are some of the application which cause this message to logged in the SQL Server Error Log.

What does these messages mean? Let me explain this with an example. Suppose ntbackup.exe is configured to take the backup of D drive. This drive has some data files related to few databases on SQL Server. Since the data files are in use by SQL Server, if these files are copied as it is the files in the backup will be inconsistent. To ensure that the database files are 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 is informed to proceed with its operation. Until the BACKUP WITH SNAPSHOT command is complete, the I/O for the database is frozen and the I/O is resumed once it completes. The corresponding messages are logged in the SQL Server Error Log.

 

翻译如下:

当任何备份服务利用SQL Server虚拟设备接口（VDI）尝试备份数据库（使用with snapshot时）或数据库文件所在的磁盘时，这个消息就会记录在错误日志（Error Log）里。 Micorsoft Backup(ntbackup.exe),卷影复制(Volume Shadow Copy VSS), 数据保护管理器(Data Protection Manager DPM)和第三方工具，例如赛门铁克Symantec 业务连续性卷（Business Continuance Volume)(BCV)，这些都是会导致这类消息记录到SQL Server错误日志的应用程序。

那么这些消息是什么意思呢？ 让我用一个例子来解释下。 假设你配置ntbackup.exe去备份D盘。这个磁盘上有一些SQL Server的数据库相关的数据文件。由于SQL Server要使用那些数据文件，因此如果这些文件在备份时复制将出现不一致。为了确保数据库文件在磁盘备份时是一致的，这些应用程序内部会使用BACKUP DATABASE [databasename] WITH SNAPSHOT命令来备份数据库。当命令执行时，数据库上的I/O会冻结并且备份应用程序被通知继续进行起操作。直到BACKUP WITH SNAPSHOT命令执行完成，数据库的冻结的I/O当备份命令一旦完成就会恢复。相应的消息也就会记录到SQL Server错误日志中。

 

后面检查发现，刚刚我们在这个时间段有PlateSpin的备份作业在运行（数据库服务器是VMware，系统管理员用PlateSpin做DR）。所以也是错误日志出现这些消息的原因。 另外，关于这个知识点，也有下面一些资料供参考、学习。

 

案例 Frozen messages while taking NT Backup for SQL databases

 

关于VDI(VSS)的介绍，可以参考下面链接

How It Works: SQL Server – VDI (VSS) Backup Resources

 

另外关于Database Snapshots (SQL Server)它也是有一些限制和性能开销的。如下截图所示：

 

 

参考资料：

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