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.

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

Related posts:

1. Revisiting the Resource Database
2. Encrypt SQL Server Database Backups
3. Restore only the data file from a database backup
4. Who created that backup file?
5. Compress database backups in SQL Server 2000 and 2005

This entry was posted in Backup, Internals, SQLServerPedia Syndication and tagged backup, internals,windows on November 28, 2010.

Post navigation

← The error is printed in terse mode because there was error during formattingCannot open user default database. Login failed →

2 thoughts on “I/O is frozen on database, No user action is required”

1. Pingback: I/O is frozen on database, No user action is required | SQL Server … - sql

2. Pingback: Wordpress Blogs Installed With Fantastico Could Be Inviting Hackers With Security Loophole - Wordpress Video Tutorials

Comments are closed.