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