IO Frozen messages while taking NT Backup for SQL databases


I recently replied on a MSDN forum post where the question was regarding why the following message was showing up for SQL database files even though no SQL Server database file was being backed up by NTBackup.

Let’s assume that you have a folder called D:\Foo on your machine which you are backing up using NTBackup. On the same driver you have SQL Server database files residing in another folder which are not being backed up. When you start the NTBackup job to perform the backup, you will find the following messages in the Application Event Logs:

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3197
Date:  1/2/2010
Time:  1:35:31 AM
User:  NT AUTHORITY\SYSTEM
Computer: <server name>
Description:
I/O is frozen on database msdb. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

The above message will be reported for each and every database on the instance which has files on the D:drive. This will be immediately followed by an equal number of messages for I/O resuming for the same set of databases.

Event Type: Information
Event Source: MSSQLSERVER
Event Category: (2)
Event ID: 3198
Date:  1/2/2010
Time:  1:35:31 AM
User:  NT AUTHORITY\SYSTEM
Computer: <server name>
Description:
I/O was resumed on database msdb. No user action is required.

The first question that would cross your mind is whether this goofy behavior is actually a bug. This is a by-design behavior of VSS (Volume Shadow) framework. NTBackup uses VSS to perform backups. When VSS is asked to perform a snapshot backup, it will ask the Writers registered with system to kick in which have files on that particular drive. Since, the drive contains SQL database files, the snapshot is created during which SQL Writer freezes IO for the SQL databases. After the snapshot is created, NTBackup finds that the database files are in the exclusion list (i.e. not being backed up) due to which the files do not reside in the physical snapshot backup file. From the SQL Server Errorlogs, you will be able to confirm this as the following message would be printed for each database whose file reside on the drive in question:

Backup Database backed up. Database: msdb, creation date(time): 2005/10/14(01:54:05), pages dumped: 1, first LSN: 319847:440:173, last LSN: 319848:24:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {‘{C0903B61-9239-4747-86C4-D4ADBED76428}3’}). This is an informational message only. No user action is required.

Notice that the number of pages dumped is 1 which means that the database was not actually backed up.

This behavior is documented under:

951288    SQL Server records a backup operation in the backupset history table when you use VSS to back up files on a volume
http://support.microsoft.com/default.aspx?scid=kb;EN-US;951288

This should NOT be a problem unless you are running into the issue mentioned in article below in which case you need to disable SQL Writer service:
937683 Error message when you try to restore a database by using SQL Server Management Studio in SQL Server 2005 after you use the Backup tool: “Restore failed for Server ‘<ServerName>’ (Microsoft.SqlServer.Smo)”
http://support.microsoft.com/default.aspx?scid=kb;EN-US;937683

This behavior will be exhibited for any application that uses VSS to backup files from a drive that has SQL Server database files. Since, SQL Database files are not like any other files on the filesystem, VSS invokes SQL Writer to perform the backups of SQL database files. If your SQL Writer service is experiencing issues, then your SQL database backups performed using VSS wouldn’t be trustworthy!

Advertisements

14 thoughts on “IO Frozen messages while taking NT Backup for SQL databases

  1. Pingback: Why is VSS complaining about SQL Data files « Troubleshootingsql's Blog

  2. Pingback: backup database sql server 2005 | BACKUP

  3. Pingback: Troubleshooting that darn backup error « TroubleshootingSQL

  4. Pingback: Troubleshooting that darn backup error « TroubleshootingSQL

  5. Pingback: Microsoft TechEd 2012–Multiple rows affected « TroubleshootingSQL

  6. I recently had an issue where the I/O was frozen, but not unfrozen. This locked up the entire SQL server and required a reboot of the physician server. Any thoughts on what would cause this?

    Like

  7. Did you see an I/O frozen message in the SQL Errorlog and no subsequent I/O unfrozen message in the Errorlog? If not, then this would indicate problems at the storage layer or with the software that was performing the backup. The I/O freeze/thaw and unfreeze commands are sent from the app to the storage layer. If the command is lost in transit (very rare scenarios and mostly bugs or race conditions), then you will land up in a situation like this.

    Like

  8. Yes, that’s what happened. The I/O frozen messages appear in the log with no subsequent unfrozen messages. The system center data protection manager is the software performing the backup, which caused this to occur. I’m wondering if this is a known problem, or a freak, on-time occurrence?

    Like

  9. Pingback: How To Backup Sql Database Using Ntbackup | Information

  10. Pingback: IO is frozen on database xxx, No user action is required | Codeba

  11. Pingback: IO is frozen on database xxx, No user action is required - 文章教程 - 编程设计 - 问说网

It is always good to hear from you! :)

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s