November 19, 2003 at 2:59 pm
We do full backups nightly and trans log backups every 2 hours to disk devices thruout the day. Backup files are copied to a remote server. The log backup device gets big during the day and the remote copies are taking so long they conflict with the Sql trans log backup towards the end of the day. To try to resolve the problem I tried a differential bkup at 12:30 pm to its own backup device on disk, then immediately backup the trans log with init to reduce the size of the trans log backup device. I'm getting error 3201 that the device is in use by another process. Operations tells me its not the remote copy conflict. Can I safely init the log device mid-day after the differential backup since I won't need the morning log backups in order to restore but only the differential?
Edited by - sunshine on 11/19/2003 3:06:29 PM
November 20, 2003 at 8:41 am
Rather than using one backup device, why not back up the tran log each time to a different file with init? Then the copies to the other server should each take the minimal time and you won't have a growing device file to worry about. A downside of your proposed method is that you lose the entire day's work if there's a problem with your differential backup, assuming that you're copying the device file over itself on the remote server.
The subject of this thread is misleading, BTW. You're asking about overwriting a file, not truncating the log...
--Jonathan
--Jonathan
November 20, 2003 at 8:57 am
Thanks for your input. I didn't want to bog down with details, but differential is to different device every other day (MWF, TT), so remote copy has previous day. Also if differential fails I skip the step to backup log with init and let 2 hour log backups continue with append to device. I didn't go to seperate log devices for simplicity sake on restore (only one device), but I may consider your stategy to keep device size down. I've read up on error 3201 and asked Ops to make sure antivirus does not scan log and backup directories. Does anyone know of anything else that might cause this error?
Edited by - sunshine on 11/20/2003 09:02:03 AM
November 20, 2003 at 3:32 pm
What is in the error log after you get the 3201 error?
--Jonathan
--Jonathan
November 20, 2003 at 5:45 pm
Not much other than failure error as shown below. I won't be back in my office til Monday so I'm not being rude by not replying... I really do appreciated your help on this.
2003-11-20 12:30:08.15 backup Database differential changes backed up: Database: TCPrd, creation date(time): 2003/06/24(07:38:28), pages dumped: 14395, first LSN: 16318:876:5, last LSN: 16318:878:1, full backup LSN: 16296:15142:3, number of dump devices: 1, device information: (FILE=1, TYPE=DISK: {'PrdTTDiff'}).
2003-11-20 12:30:08.25 spid62 BackupDiskFile::CreateMedia: Backup device 'E:\Backup\TCPrdLog.BAK' failed to create. Operating system error = 32(The process cannot access the file because it is being used by another process.).
2003-11-20 12:30:08.26 backup BACKUP failed to complete the command -- backup log which will truncate inactive log
-- init will overwrite morning log
-- To restore: previous nights full bkup must be restored first
-- then differential bkup must be applied before applying aftenoon log bkups
backup log TCPrd To TCPrdLog WITH INIT
2003-11-20 13:30:05.26 backup Log backed up: Database: TCPrd, creation date(time): 2003/06/23(12:10:34), first LSN: 65060:41:1, last LSN: 65060:43:1, number of dump devices: 1, device information: (FILE=4, TYPE=DISK: {'TCPrdLog'}).
November 21, 2003 at 2:30 pm
Sounds like something has marked the file 'E:\Backup\TCPrdLog.BAK' in use and cannot be opened or written to. Could be a bad thread in memroy still locking and might requried restarting the server to clear.
November 25, 2003 at 2:00 pm
The cure for the error was to put WAITFOR DELAY '00:00:05' right before backing up the log. Antares686 may have been right... I suspect the differential backup still had the file locked and the 5 second delay allowed enough time for it to complete.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply