| Author |
Topic |
|
vaddi
Posting Yak Master
145 Posts |
 Posted - 2007-05-09 : 12:06:04
|
| Hello,I have a hourly log backup scheduled for a vmware DB. It keeps failing at 3.00 Am every night. It fails only at that particular time.The error that I see are:18204: Spid 108 : Backup Disk File :create media: backupdevice 'D:\mssql\backup\vmware\vmware_log.bak'failed to create. Operating system error =32.(The process can not access the file because it is being use by another process).3041: Backup failed to complete the command BackupLog[vmware] to disk =n'D:\mssql\backup\vmware\vmware_log.bak' with noinit, nounload,skip , stats=10 , noformat,no_turncate.I thought some process might be locking it at that and changed the hourly log backup time and scheduled it for 3.15 AM. But still it fails at that time only. The remaining backups at 4.15 , 5.15 are good.What might be the problemTHanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-05-09 : 12:08:34
|
| Maybe there is a disk backup - maybe to tape at that time?Maybe it's just being copied somewhere else?Is there another backup process going on to the same file? Possibly in a maint plan so you can't see what it's doing?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-09 : 12:21:28
|
| Here's a question for you: Why are you using the same filename for the log on every dump? You know this overwrites the previous file, right? Which would make the file useless for recovery unless you ran a backup in the prevous hour.e4 d5 xd5 Nf6 |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2007-05-09 : 12:39:57
|
| There is tape backup running at that time. But the log backp of all the other DB's are good at that time i.e 3.00 AM. There are no additional maintenance plans running.Might be a silly question , but I have a backup of whole DB once evey night and have got hourly log backps which overwrite every hour. I was thinking if I need to recover , I can recover the last night backup and use the last hourly log backup. Am I thinking in the wrong way.Thanks for the help. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 13:20:06
|
| maybe your daily Full backup of that database is still running when the Log backup starts? (I thought that just Blocked, rather than Failed, but might be worth checking)"I was thinking if I need to recover , I can recover the last night backup and use the last hourly log backup. Am I thinking in the wrong way."Yup, 'fraid that IS the wrong way!You need to recover a FULL backup and then EVERY INDIVIDUAL TLog backup since.If you make DIFFERENTIAL backups then you can recover the most recent Full Backup, and a subsequent Differential Backup.The TLog backups route is more flexible - you can recover an "old" Full Backup, providing you then restore EVERY subsequent TLog backup - so a "broken" Full Backup doesn't matter, providing you have an earlier one and that all the subsequent TLog backups are intactEdit: changes are in italicsKristen |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2007-05-09 : 13:26:58
|
| Thanks for the explanation kristen. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-09 : 13:32:34
|
| This is almost certainly caused by the tape backup or virus scan. The file can not be in use when the log or database backup occurs. So if the tape backup software happens to be on that file when the log backup runs, the log backup will fail. The fact that your other log backups are working fine doesn't mean that the tape backup isn't what is causing this. Nothing can be accessing the file when the log backup runs.I'd suggest checking the tape backup software to see how long it is spending on that file. Once you know the length of time it will be accessing the file, you can figure out if you want to change the schedule of the log backup or the schedule of the tape backup.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
blindman
Master Smack Fu Yak Hacker
2365 Posts |
Posted - 2007-05-09 : 14:16:36
|
| I'd suggest not overwriting previous log backups. This will not only solve your problem, it will actually make the entire exercise useful.'Cause right now, you'd have a hard time explaining to your boss why you are unable to restore the database even though you've been making log dumps all day long.e4 d5 xd5 Nf6 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 15:06:34
|
| "I'd suggest not overwriting previous log backups"+1 to that ... |
|
|
|
vaddi
Posting Yak Master
145 Posts |
Posted - 2007-05-09 : 16:10:37
|
| Thanks Tara.I have one question. Can the Virus Scan on the DB's also cause for the backup to fail.Thanks in advance. |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-09 : 16:14:03
|
| I reckon that anything that can be reading the file, when you want to open it for Appending, can block you.Thus changing your backup tactics to use a unique filename for each new backup cannot fail because of trying to open a pre-existing file that is already open for reading by some other process!Kristen |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-05-09 : 16:22:15
|
| Agreed. I always use a new file name. I only run into this sort of problem when I am attempting to copy the file to another location and the tape software is currently backing it up to tape.Tara Kizerhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2007-05-09 : 16:47:28
|
| I sometimes see a similar problem when the backup job is unable to delete an old backup file that is being backed up to tape at the the time it tries to delete it.CODO ERGO SUM |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-05-10 : 04:32:26
|
| FWIW when we are coping to another location, or somesuch, we first MOVE the files to a sub-folder, then COPY them to the remote location, then MOVE them to a "Done" folder (or delete them if that's the requirement).This means that any in-progress Write operation, or other file lock, prevents the file being Moved, so that file gets processed next time around.Kristen |
|
|
|
|
Hourly Log backup failure at Night only