Have you ever encountered the message “I/O is frozen on database” and “I/O was resumed on database” while working with SQL Server? If so, you might have wondered what these messages mean and why they appear. In this article, we will explore the reasons behind these messages and shed some light on their significance.

Firstly, it’s important to note that these messages are informational and generally do not require any user action. They are generated when there is any software taking volume-level backup, such as BackupExec, Veeam, Microsoft Data Protection Manager (MSDPM), or Azure Backups.

When a volume-level backup is initiated, the software interacts with SQL Server Writer to obtain a backup of the files. During this process, SQL Server freezes the I/O (input/output) operations on the database to ensure a consistent backup is taken across the volumes. This coordination between the Volume Shadow Copy Services (VSS) and SQL Server helps in creating snapshots for the volume.

The backup messages typically appear in a sequence, as shown in the example below:

Message 1: I/O is frozen on database model. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.
Message 2: I/O was resumed on database msdb. No user action is required.
Message 3: Database backed up. Database: model, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 33:208:37, last LSN: 33:240:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{E262A34B-E3DA-467C-A881-BBAED5D79ACA}1'}).
Message 4: BACKUP DATABASE successfully processed 0 pages in 4.705 seconds (0.000 MB/sec).

These messages are generated for all databases that are being backed up. In the example, the system databases (master, model, and msdb) were backed up, hence the appearance of these messages for each of them.

It’s worth noting that the backup type mentioned in message #3 is VIRTUAL_DEVICE = {GUID}. This indicates that the backup was taken using a virtual device, which is a result of the interaction between the backup software and SQL Server Writer.

Unless the backups are causing issues with the application, these messages can generally be ignored. They serve as an indication that SQL Server has frozen the I/O operations temporarily to facilitate the creation of consistent database backups across the volumes.

Have you come across any interesting SQL Server backup messages? Feel free to share them in the comments below!