I/O in the database has been frozen, and no user operation is required

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. SupposeNtbackup.exeIs 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 issuesBackup database [databasename] With SnapshotCommand 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. UntilBackup with snapshotCommand 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.

IfNtbackupIs 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 shocould 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.

I/O on the database has been frozen and no user operation is required.

One of the questions I have recently replied to is that you need to understand the meaning of these messages in the SQL Server Error Log.

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.

I even saw this message on a data warehouse instance in my environment. This message is recorded in the error log. When any backup service uses the SQL Server Virtual Device Interface (VDI) to attempt to back up the database (with snapshot)/run in the database file. Microsoft elastic backup(ntbackup.exe), Volume Shadow Copy (VSS), Data Protection Manager (DPM), and third-party work such as Symantec Business Continuity Volume (BCV) are some applications that cause this message to log to the SQL Server Error Log.

So what do these messages mean? Let me explain it with an example. Configure ntbackup.exe to backup the D disk. This disk contains database-related data files on SQL Server. Because SQL server needs to use those data files, if these files are copied during backup, there will be inconsistencies. To ensure that the database files are consistent during disk backup, these applications use the backup database [database name] with snapshot command internally to back up the database. When the command is executed, the I/O on the database will be frozen and the backup application will be notified to continue the operation. After the backup with snapshot command is executed, the frozen I/O of the database is restored once the BACKUP command is completed. The corresponding messages are recorded in the SQL Server Error Log.

If ntbackup is executed during office hours. Users accessing the database will encounter some connection problems. This is one of the reasons why the backup service in the production system is not executed during office hours. As I mentioned earlier, in my environment, I have seen that all these messages are generated by performing backup during the rest time.

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