SQL SERVER – What do These Messages Mean – “I/O is Frozen on Database” and “I/O was Resumed on Database”?

One of my clients contacted me and asked me the reason for this message? And my first reaction was, did you keep the database in very cold weather? It might be freezing over there and that’s why SQL is saying:   “I/O is frozen on database” and “I/O was resumed on database”? Did you check the air conditioner temperature?

SQL SERVER - What do These Messages Mean - “I/O is Frozen on Database” and “I/O was Resumed on Database”? frozen-800x435

Jokes apart. Let’s discuss the message. Let me paste the complete message and I am showing only one such combination.

Message 1I/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 2I/O was resumed on database msdb. No user action is required.
Message 3Database 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’}). This is an informational message only. No user action is required.
Message 4BACKUP DATABASE successfully processed 0 pages in 4.705 seconds (0.000 MB/sec).

This sequence of messages would be present for all databases which got backed-up. I had only four system databases on my test server. I fired disk shadow to take a backup of C drive and once the backup was completed, we can see three such pairs, each with the same four messages. Since we can’t take the backup of tempdb, we are seeing only master, model, and msdb. I have highlighted them with a different color so that it is easy to focus.

SQL SERVER - What do These Messages Mean - “I/O is Frozen on Database” and “I/O was Resumed on Database”? db-frozen-01

THE ANSWER – Frozen

Four messages which are shown in this blog are generated when there is any software taking volume-level backup. I have used DIskshadow to take C drive backup and all the database for which the files were in C drive got backed up. These software use VSS (Volume Shadow Copy Services) which interacts with SQL Server Writer to get backup of those files. The key part is the backup type mentioned in message # 3 – VIRTUAL_DEVICE = {GUID}

If you are seeing such messages, they are information messages and generally can be ignored unless the backups are causing issues with the application. These messages indicate that SQL Server has frozen the IO so that snapshots can be taken for the volume. This is coordination between VSS and SQL Server so that consistent database backup is taken across the volumes. If you are using software like BackupExec, Veeam, Microsoft Data Protection Manager (MSDPM), Azure Backups then it is normal to see these messages.

Are there any such interesting messages which you can share?  Please comment and let me know.

Reference: Pinal Dave (https://blog.sqlauthority.com)

, , , ,
Previous Post
SQL SERVER – Install Error – The Account Running SQL Server Setup Does Not Have Administrator Rights On the Computer. To Continue, Use an Account With Administrator Rights
Next Post
SQL SERVER – PolyBase Error Msg 46530 – External Data Sources Are Not Supported With Type GENERIC

Related Posts

2 Comments. Leave new

Leave a Reply

Menu