?
Solved

I/O is frozen on database []. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup.

Posted on 2014-01-30
15
Medium Priority
?
5,760 Views
Last Modified: 2016-02-18
I am getting a sporadic message in my sql server logs about frozen I/O on all databases. Then about 4 seconds later, I/O resumes, and all the databases get backed up, and a dbcc checkdb is run.  Why is this happening?

I have verified that our Symantec backup does not run at this time, nor does my scheduled SQL backup routine. The Symantec backup does take a snapshot prior to the scheduled backup process, but doesn't take snapshots throughout the day.  

This particular server does not have a lot of users, it has an SSRS database and runs several SSIS/SSAS jobs.
0
Comment
Question by:jleenotlo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 2
  • +1
15 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39821267
My understanding is this is a function of Volume Shadow-copy Service (VSS) and tools that leverage VSS, such as the snapshot for Symantec most likely does.

EDIT: here is a write-up of how VSS backup works on SQL Server.

In addition, the following may be a useful read:
http://technet.microsoft.com/en-us/library/cc966520.aspx
0
 
LVL 1

Author Comment

by:jleenotlo
ID: 39821295
I've read that too, but the I/O message is not occurring at the same time as when the shadow copy would take place, in fact, they're usually hours apart.  

The Symantec procedure runs at 11pm, my SQL backup proc runs at 7:30am, and the I/O issue is sporadic. It's happened at 8am, 3pm, 11am, and all other times over the last couple days, but i've never seen the message more than twice in one day.
0
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 39821352
Have you double checked the Shadow Copies configuration on the server?  Just to make sure that there are no scheduled copies outside your backup window.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:jleenotlo
ID: 39821377
I've just checked and the Microsoft Software Shadow Copy Provider service is not enabled on the server in question.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39821390
Is the system virtualized?
0
 
LVL 1

Author Comment

by:jleenotlo
ID: 39821397
No, it's not.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39821414
Hmm.  Very strange.  I am guessing you already checked SQL Agent jobs and maintenance plans to make sure their is no job getting stuck on retry during business hours, but you can double check that.  I just re-read your question and see that the databases backup as well as dbcc checkdb is run.  It definitely suggests this is part of a routine.
0
 
LVL 1

Author Comment

by:jleenotlo
ID: 39821431
The SQL agent jobs that do maintenance (like backup) are not scheduled during business hours, and these maintenance jobs have not been failing on off hours either.  I've had a few ssis jobs failing throughout the day, but they are not related to any backups.

The backup and dbcc checkdb seem to be running as a direct result of the I/O issue.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39821484
Are you seeing other messages (errors) in the log around the same time?  I have seen one post suggesting issues starting after upgrade of Backup Exec, but have not seen the automatic backup/dbcc checkdb.
0
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 39821494
P.S. make sure that Windows backups are not setup as well, checking Task Scheduler also.
0
 
LVL 1

Expert Comment

by:kevwit
ID: 39821618
What service pack to you have applied?
0
 
LVL 1

Author Comment

by:jleenotlo
ID: 39821680
There are no errors immediately preceding the instances of I/O Frozen messages.

Windows backup is not even installed because we use Symantec, and the 2 of them can interfere with one another.

Windows Server 2008 R2 Standard, Service Pack 1
SQL Server 2008 R2; Build: 10.50.1777.0
0
 
LVL 1

Expert Comment

by:kevwit
ID: 39822673
Please apply at least SP1 to that instance. You have applied CU7 to the RTM version, but SP1 fixes a lot of issues.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 39822696
Hi,

Although you're fairly certain that it isn't the backups or anything, I'd be inclined to double check that the Symantec backup using VSS isn't trying to backup the database files themselves, the mdf and ldf files..

I'd also double check the windows scheduled jobs as well.

Another thing to check is your anti-virus. Check that it is excluding the .mdf & .ldf files from its scanning and monitoring.

And lastly, do double check that the database properties, that autoclose is set to false, and autoshrink is set to false.

HTH
  David
0
 
LVL 1

Author Closing Comment

by:jleenotlo
ID: 39842149
Thank you for your feedback on my question. After monitoring the systems for a about a week, I realized that there was a shadow copy starting immediately before many of the instances of I/O frozen messages.  So we looked at Symantec and unchecked the options for SqlServer specific backups. This has stopped the problem for 24hrs now. So we'll continue to monitor and hopefully its all fixed.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question