August 23, 2013 at 7:18 am
Hello,
On the SQL Server the Event Viewer shows the same messages and errors every evening between 22:05:00 and 22:08:00. The following information messages are shown for every database:
"I/O is frozen on database <database name>. No user action is required. However, if I/O is not resumed promptly, you could cancel the backup."
"I/O was resumed on database <database name>. No user action is required."
"Database backed up. Database: <database name>, creation date(time): 2003/04/08(09:13:36), pages dumped: 306, first LSN: 44:148:37, last LSN: 44:165:1, number of dump devices: 1, device information: (FILE=1, TYPE=VIRTUAL_DEVICE: {'{A79410F7-4AC5-47CE-9E9B-F91660F1072B}4'}). This is an informational message only. No user action is required."
After the 3 messages the following error message is shown for every database:
"BACKUP failed to complete the command BACKUP LOG <database name>. Check the backup application log for detailed messages."
I have added a Maintenance Plan but these jobs run after 02:00:00 at night.
Can somebody give me a clue how I can get rid of the error messages?
Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
Thanks in advance.
August 23, 2013 at 7:28 am
evdb68 (8/23/2013)
Can somebody give me a clue how I can get rid of the error messages?Where can I find the command or setup which will backup all databases and log files at 22:00:00 in the evening?
Most likely you can find one or more jobs in the SQL Agent section. Look at the schedules to see when each job is scheduled to be executed.
If there are no jobs running at 22:00 you'll have to look for an external (third party) application taking these backups.
August 26, 2013 at 12:37 am
Thanks for your answer.
The only Agent jobs which run a backup are in the night starting at 2:00:00.
Until now external programs which do a sql backup are not present.
This morning I noticed that Veeam-backup is starting around 22:00:00. Maybe this has to do something with the errors.
We are running multiple servers in VMWare and Veeam-backup is used to make a backup of this VM's.
Until now I thought Veeam-backup makes a backup of a whole VM-server.
I will investigate of Veeam-backup maybe triggers SQL servers to make a backup of its own.
August 26, 2013 at 10:56 am
Any backup product that uses Volume Shadow Copy Services (VSS) cause this to happen so that they can get a consistent backup. This blog post, http://sqlservertimes2.com/?p=816 mentions this for CommVault, but also contains some links to more MS documentation about VSS.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
August 26, 2013 at 12:21 pm
Jack Corbett (8/26/2013)
Any backup product that uses Volume Shadow Copy Services (VSS) cause this to happen so that they can get a consistent backup. This blog post, http://sqlservertimes2.com/?p=816 mentions this for CommVault, but also contains some links to more MS documentation about VSS.
This should not break the log chain - but it apparently does. I believe there was a bug with one of the products causing these kinds of issues - but I cannot recall which product it was.
Either way, unless you have a very large database where you need the ability to snap the LUNs for backups I would recommend disabling those backups and relying solely on native SQL Server backups.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 27, 2013 at 12:11 am
Thanks for your answer. These make something clear to me.
I will look into it and will get back if I have found a solution.
September 16, 2014 at 10:25 am
Hi Jeffrey
I want to be sure I follow. Like many SQL admins, I was given SQL admin tasking by sheer team size (ok, we've got three people, umm ... Jen, you're the SQL admin! 🙂 We have three production databases. One is their mission critical app. I have that set to transaction log backups every 15 minutes (and the other two apps to every four hours) between the hours of 4 AM and 11 PM. I have backups running at 2 am. During the nightly backup, I included in the maintenance plan to shrink the databases, reorganize the indexes, check the db integrity, and then run a full backup.
The logs report those all running successfully.
At 2:38 AM however, errors are thrown in the logs (as referenced in the subject of this thread) -- the I/O is stopped then starts back up, some messages state backups were successfull, followed by errors are thrown stating the BACKUP LOG was not successful. I believe this is the VM snapshot the sys admin is doing each night.
From what I understand with your post, having the I/O start and stop could corrupt the transaction logs (If I understood that correctly). Or, since there is no activity on the server at 2:38 AM (typically --- in a very RARE instance there could be one person on and active), should we not worry about these errors in the nightly logs?
Thank you for any advice you can offer!!
Jennifer, the reluctant SQL admin 🙂
September 16, 2014 at 11:17 am
jennifer.elkhouri (9/16/2014)
Hi JeffreyI want to be sure I follow. Like many SQL admins, I was given SQL admin tasking by sheer team size (ok, we've got three people, umm ... Jen, you're the SQL admin! 🙂 We have three production databases. One is their mission critical app. I have that set to transaction log backups every 15 minutes (and the other two apps to every four hours) between the hours of 4 AM and 11 PM. I have backups running at 2 am. During the nightly backup, I included in the maintenance plan to shrink the databases, reorganize the indexes, check the db integrity, and then run a full backup.
The logs report those all running successfully.
At 2:38 AM however, errors are thrown in the logs (as referenced in the subject of this thread) -- the I/O is stopped then starts back up, some messages state backups were successfull, followed by errors are thrown stating the BACKUP LOG was not successful. I believe this is the VM snapshot the sys admin is doing each night.
From what I understand with your post, having the I/O start and stop could corrupt the transaction logs (If I understood that correctly). Or, since there is no activity on the server at 2:38 AM (typically --- in a very RARE instance there could be one person on and active), should we not worry about these errors in the nightly logs?
Thank you for any advice you can offer!!
Jennifer, the reluctant SQL admin 🙂
The reason why the process (VM Snapshot in your case) freezes I/O on the database file(s) is so that there won't be corruption in the snapshot being taken. Essentially freezing I/O on the snapshot means the database will be in the same state when the snapshot finishes as it was when it starts, if I/O was not frozen you run the risk of having partially completed transactions (corruption) in the database on the snapshot. You don't want that because the snapshot is part of your DR process and if you have to revert to that snapshot and I/O wasn't frozen, your database will be suspect and unusable without intervention. I had this happen when we did a DR test and our NETAPP snap process isn't freezing I/O so we had some corrupt databases in our DR site.
You shouldn't get your backup chain broken by this, but the only way to be sure is to do a full restore and then restore your logs up to and past the point of the snapshot.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 16, 2014 at 12:37 pm
I believe the problem you are having is related to the snap technology being used - which is causing the log chain to be broken. You need to find out specifically what the system admins are using - and what it is setup to actually do...as that is causing a break in the log chain.
Once the log chain is broken, no further transaction log backups are possible until you perform a full backup.
The IO freeze/thaw process is - as Jack has stated - required to make sure you have a consistent backup. Snap backups don't actually create a database backup - they take a 'snapshot' of the volume where the database and log files reside. When you perform a restore from a snap - all you are really doing is putting the copy of the mdf/ldf files back as they existed at that point in time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 16, 2014 at 12:51 pm
Thanks you both!!! Very helpful posts!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply