asked
I/O is frozen on database
Hi, I used to take my server backup using a customed blocklevel backup software which used VSS in order to take SQL Server backup.I have removed that software now.Still I see some messages in SQL Server Error Log like 'I/O is frozen' --'I/O is resumed' and later on database backup messages. I checked backupmedia table and found GUID in physical_device_name column and null value in logical_device_name.I checked and found that there was no job in sysjobhistory which should indicate that any backup job may be the reason of this error. Can anyone explain how I can get rid of those I/O frozen and I/O resume messages.Perhaps the reson may be that VSS might be the culprit. Any idea I can explore further.
I've seen those when third party backup tools are taking backups of the drives where the database files live. They 'freeze' the file whilst they take a snapshot, hence the SQL error log entries. Speak to your system/network admins and see if they have any scheduled backup tasks running for that server. Maybe think about moving those database files to a volume that isn't backed up by these tools, and schedule your own SQL database backups.
And when I say 3rd party I mean non-SQL. Windows Volume Shadowcopy Service (VSS) causes this too. As @Chris Shaw says, it is using block level copy techniques.
@inder : SAN snapshots, System Centre Data Protection Manager, VSS, any block level copy - check if any of these are still running. If so, see if you can exclude the data file directories, and take your own native backups.
I just recently saw the same issue, or something very close. It was with a product does a snap management via SAN technology. So something you may want to look into is if there is some sort of replication that is happening on the Disk side that is not a SQL Server soultion. Funny thing is the prodcut that I saw this with they were also running a backup soultion, doing full's and t-log backups. However, they did not support a diff, because it us just taking a snap shot of all the blocks. Where I really saw issues was with the t-log management, they did not support a truncate when the log was backed up. So the end result was the T-Logs would never be emptied, and a t-log drive would eventually fill up. The only fix was to run a t-log with native SQL Server with the truncate, but then the backup chain was broke. In short, I have seen this when something is trying to get a block level copy of your disk. Hope that it helped a little.
