September 11, 2007 at 8:52 am
I posted once before on this topic, but didn't get much feedback, so here's a fresh post with hopefully better information.
I have a replicating system that once a week its distribution backup fails reporting that the (automatically generated timestamp-named) filename already exists. The full backup job runs daily. It resumes successfully the next day without any manual-intervention. It has happened 3 weeks in a row, always the same day of the week, Sunday morning.
The only hint to this pattern that's different from other days is that on the morning before (and after the dist's regular backup on that morning) I run a stored procedure to indexdefrag the highest fragmented indexes on the database being replicated, with filesize management around it (a shrinklog and full backup completed after the defrag). I don't know what in if anything in that activity would cause the subsequent distribution backup to hiccup. The replicating database's maintenance plans run error-free, and the distribution plan was recreated and still throws its Sunday morning error.
I can post the stored procedure and steps I run before and after if you want full details of the job. It's the strangest thing I think I've seen SQL 2000 do.
September 11, 2007 at 2:39 pm
Could it be that the saturday backup job is still running because of the ongoing stored procedure?
Are you taking 2 full backups on saturday (one regular, one stored procedure after defrag)?
September 12, 2007 at 7:42 am
The Saturday jobs run at 4:30 am and are done before 5am. The defrag runs at 5 am, and when it's done a full backup is done of just the replicating database where the defrag was done.
October 1, 2007 at 9:34 pm
Are you sure the job is done? Just because it's done on other mornings doesn't mean it's not a problem on that day.
Also, you aren't defragging the distribution database, you're doing the database that is being replicated, right? All of those transactions will run through the distribution database, but that shouldn't be a problem.
Can you post the jobs (descriptions), times start and finish, and what's affected in them? Can you post the error as well?
October 2, 2007 at 7:46 am
I do get a backup file despite it reporting a failure. The resultant problem was that because the job found a failure it had stopped deleting old backup files and this was on a system awaiting new drive space so only two full backups of the main database were being taken. The stop of the deletion step due to it finding an error caused the system to run out of space to take further backups of the main database. I worked around it by putting distribution in its own backup so it can report a failure all it wants and not effect the main db.
The distribution backup plan has a Fri 1 am integrity check, and a daily 4:30a full backup, that's it. The main db also gets a full backup daily at 4:30a in its maintenance plan. On Sat at 5am indexes are defragged on the main database only via a separate scheduled job, and distribution is not called by the procedure. On Sunday's 4:30 run distribution reports the failure "The file Q:\backups\distribution\distribution_db_200709300430.bak already exists. This backup command is ignored." I do get the backup, I just don't know why it thinks it's being executed twice, I guess. The other two plans are the main db only, and all system tables.
October 2, 2007 at 7:50 am
Oh, the index defrag is done before 6am on Sat morning if that's what you were asking and all full backups are completed prior to 5am across the board. Nothing is running after that that I can tell.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply