Sql-server – I/O is frozen on Database & also I/O was resumed on Database on daily basis in log with complete Backup

backupmaintenance-planssql serversql-server-2012

I would like to say that as on daily basis i got the message in my log file.

First The I/O is frozen on Database (DatabaseName) message appears in log. The message is like

I/O is frozen on Database (DatabaseName).No User action is required.
However, if I/O is not resumed promptly, you could cancel the backup.

And then I/O was resumed on Database(DatabaseName) message appears like

I/O was resumed on Database (DatabaseName). No user action is
required.

And finally the backup has done of Database with successfully with like this message

No User Action is Required

It is My production Audit database and having the software Environment is like:

Windows Server 2012 R2
SQL Server 2012
VMware Tools

My production Database "Recovery Model" is "Full" recovery model. And from which login ID in SQL Server, the Maintenance Plan Backup was setup.That has "sysadmin" privileges for all databases.

The Backup has setup through "Maintenance Plan" Backup at specific time schedule like 4.00 P.M. at every day. And Always Backup went through successfully at their schedule interval.

Why such kind of log message generate in at schedule backup time.
Is this kind of message will impact to my Production Database performance.
Will be any issues in (Backup/Restore) data consistency in my production database.

Any suggestion will be appreciated.

Best Answer

I believe its just a message and not an error unless and until the task is getting impacted when this message is logged in the error log:

We also encountered the same for one of our production server, where windows admin team enabled the SQL Server backup through VSS (Volume Shadow Copy Services). And when this process runs it freezes I/O temporarily to take shadow copy and release it back once process is complete.

Major cause being "I/O on the database is frozen in order for any backup service using Virtual Device Interface (VDI) and commiting a sql snapshot backup." as mentioned here

For a better understanding how this process works please read How It Works: SQL Server - VDI (VSS) Backup Resources2

Therefore i believe you should start looking for process like Volume Shadow Copy (VSS) OR Data Protection Manager (DPM) running on the server and discuss with system admin team for what is enabled and to ensure nothing is impacted.

Related Question