【翻譯】資料庫上的IO已凍結，不需要任何使用者操作

I/O is frozen on database, No user action is required

I recently responded to one of the questions in which the user wanted to know what these messages in the SQL Server Error Log mean.

?

1 2	spid92,Unknown,I/O is frozen on database [databasename]. No user action is required. spid92,Unknown,I/O was resumed on database [databasename]. No user action is required.

Even in my environment I have seen this message in some of the Data warehouse instances. 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.

If the ntbackup is issued during business hours, the users accessing the database will surely have some connectivity issues. This is one of reasons why the backup on production systems should not be done during business hours. Like I mentioned earlier, in my environment I have seen these messages but during the regular downtime for backups.

資料庫上的IO已凍結，不需要任何使用者操作

   

我最近回複過的其中一個問題就是使用者要瞭解在SQL Server錯誤記錄檔中這些訊息的意思。

   

spid92,Unknown,I/O is frozen on database [databasename]. No user action is required.

spid92,Unknown,I/O was resumed on database [databasename]. No user action is required.

   

甚至在我的環境中我也曾在資料倉儲執行個體上看到過這條訊息。這條訊息記錄在錯誤記錄檔，當任何備份服務利用SQL Server虛擬設備介面（VDI）嘗試備份資料庫（with snapshot）/運行在資料庫檔案所在。 微軟備份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 [資料庫名稱] WITH SNAPSHOT命令來備份資料庫。當命令執行時，資料庫上的I/O會凍結並且備份應用程式被通知繼續進行起操作。直到BACKUP WITH SNAPSHOT命令執行完成，資料庫的凍結的I/O當備份命令一旦完成就會恢複。相應的訊息也就會記錄到SQL Server錯誤記錄檔中。

   

如果ntbackup在辦公時間執行。訪問資料庫的使用者將一定會遇到一些串連問題。這也就是為什麼生產系統中的備份服務不在辦公時間執行的原因之一。正如我先前所述，在我的環境中，我看到過這些訊息不過都是在休息時間執行備份產生的。

   

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