March 3, 2005 at 3:17 pm
Hi,
I am having two mainenance plans. One does a full backup every 6 hours and the other does transaction logbackup every 5 minutes. I have ensured that when one is run the other is is not running. Both these plans back up over the network to an ArchiveServer (Windows 2003). Almost every other day i am having the following error.
Executed as user: ABCWorld\sql7service. Cannot open backup device 'ABC'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] (Error 3201) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Error on SQL server Logs
BackupDiskFile::CreateMedia: Backup device '\\Archiveserver\backup\ABCLogs.BAK' failed to create. Operating system error = 32(The process cannot access the file because it is being used by another process.).
After that the error keeps occuring every time the job runs. When i look in the ArchiveServer with the Computermanagement tool i see that the ABClogs.bak file is left open. Once i close that the jobs run fine again.
the Antivirus in ArchiveServer is turned off for that folder. The transaction log back up just runs for a maximum of 4 Minutes. in the full back up the step that backs up the transaction log is the one that fails.
March 4, 2005 at 2:52 am
yo should try to figure out why the file is left open.
Do you cancel the backup command when it runs for more than 4 minutes ? What proces is accessing the file?
JP
March 4, 2005 at 3:06 am
I would stop using the maintenance wizard and write your own scripts to do the backups. The backups should be with init and teh first trab log backup to a different file also with init. Are there any network backups running that may be opening the file or a restore to another server?
March 4, 2005 at 4:26 am
Hi,
It should be working fine even with SQL EM. I have similar backup strategy wherein I take a Full Backup twice a day and T-Log backups every 15 mns.
Kavitha -- You may want to try out this :
Just create a new Maintainence Plan pointing your backups to your local server with the same strategy and see if it works fine.
And when do you delete your old backup files (Full and T-Log). Please let me know so that I can help you better.
Let me know of your progress.
--Kishore
March 4, 2005 at 7:27 am
Hi,
do you copy the backup-files to another server? If yes, maybe that's not finished.
Are you creating a new file for every log-backup, or do you reuse the same file?
karl
Best regards
karl
March 4, 2005 at 8:06 am
Karl,
Thanks for your respose. yes i copy the back up file to another server. i reuse the same file in transaction log back up. I recreate the Log file every time i run the Full backup.
Kavi
March 4, 2005 at 8:13 am
HI Kishore,
i do not have enough space in the local server to start the back up there.
The last 3 copies of the back up file are retained. The rest are deleted after a tape backup. The time for Tape backup does not overlap the regular backups.
For Log Backup the same file is used again and again. Transaction log back up overwrites the Logbackup file and the full backup deletes it and creates a new one.
The backup failing frequency has increased. The failure keeps comming with the transaction log backup alone.
Should i be creating a new Maintenance plan?
THanks,
kavitha
March 4, 2005 at 8:40 am
Hi,
how long does the copying run? i think the copy-job locks the log backup file...
perhaps you need to split the file containing the log-backups, so their size stays smaller.
karl
Best regards
karl
March 4, 2005 at 8:49 am
My guess would be that the last but one log backup takes too long.
There is a posibility to get more information from the maintenance plan.
Let the job that does the log backup make a report. You can do that in EM.
Select the log backup job, go the the step tab, select the step, use edit, go to advanced.
Enter an Output file here and use the append option.
The output file will now give the same output you get when you do the backup by hand.
Maybe now you will get an error because two jobs try to access this file, but you will get some information what the jobs do.
Joachim.
March 4, 2005 at 9:26 am
I had encountered this problem sporadically on one server backing up to a SAN disk. Check your transaction log maintenance plan to see if the "Verify the integrity of the backup upon completion" is checked. If it is, uncheck it and see if the error goes away. If it does you may have encountered a "bug" in MDAC 2.8. Here's the KB link if this is the case (you'll need to contact Microsoft for the hot-fix):
http://support.microsoft.com/?kbid=827452
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
March 4, 2005 at 1:32 pm
>>
For Log Backup the same file is used again and again. Transaction log back up overwrites the Logbackup file and the full backup deletes it and creates a new one.
<<
WHOAH!!! You are overwriting your transaction log backups??? In order to recover with transaction log backups, you MUST have ALL transaction log backups since your last full backup. If you are overwriting them before they are written out to tape, they are of no use whatsoever!
As Athugar suggested, your best solution is to build your own backup strategy using scripts/stored procedures rather than maintenance plans, and writing EACH backup to a SEPARATE file. Use a date/time stamp in the filename. Makes management much, much easier.
Steve
March 5, 2005 at 12:40 am
Kavitha --
I think this topic is getting rather very confusing.
I would reccomend you create a ONE New Backup Maintainence Plan for the Full and T-Log Backup.
Follow the exact steps I show you now :
1. First and foremost thing share the folders on your ArchiveServer (Windows 2003) on your original server and see to it that is accessible from your Original Server and has write permission.
2. Go to Maintaience Plans under Management in EM. Select the database that you want to create the backups for.
3. Select the option 'Backup the database as the part of Maintainence Plan.' Under Schedule, change the schedule as Take Backup every 6 hours.
4. Select the backup directory in which to store the backup file. Here type the Drive name which is a shared drive on your Server and the folder if you have store the backup to a particular folder.
Also here Specify that you want to delete the old backup files after 8 Hours. So that at any point of time you have only the Latest Full Backup available with you.
5. Select 'Backup Transaction Log as part of Maintainence Plan'. Here Again change the schedule as Take Backup every 10 minutes. Specify the backup directory where you want to store the T-log backup files.
Also again here Specify that you want to delete the old backup files after 8 Hours. So that at any point of time you have only the Latest T-Log Backups available with you.
DO NOT SPECIFY THE BACKUP FILES NAME ANYWHERE AS IT WILL AUTOMATICALLY CREATE NEW BACKUP FILES WITH DATETIME TIMESTAMP.
Two jobs will be created automatically. One for your Full Backup and other for your T-Log Backup. You can now keep a check on your Jobs and see that it runs fine.
I also have the same setup on my Server. Just the time interval differs and it is working perfect since last 4 years.
Hope this helps. Let me know of your progress.
--Kishore
March 9, 2005 at 11:06 am
Thanks for all your Help. I have created a new DB maintenance plan. I am monitoring the system to check if the error occurs again. Its been two days. No problems so far.
Thanks
Kavitha
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply