Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have a very large Merge publication for a data warehouse with some large (.bcp files on the order of 20GB) fact tables that is causing us tremendous trouble.

Specifically, we created a publication and a subscription, and things ran OK w/ a couple hiccups (disk space). After the first hiccup (log space exhausted) we saw this:

You must rerun snapshot because current snapshot files are obsolete.

On restart, it did pretty much what one would expect (went through the process, skipping things it had already done).

At the end we saw the 'nothing to do' message, but immediately it went back into the ftp/apply process, failing b/c when it tries to apply the .sch file 'can't drop the table because it is being used in replication'.

So things are obviously hosed. Restarting from zero might be advised but we did the 'remove publication' sproc on the offending subscriber last night so we should have been clean.

Also, this whole process (downloading and applying) took 9 hours, so if possible I would like to get to the root of it before restarting the process. Why would the snapshot have become obsolete in such a short period? What could have gone awry? If you have any questions or need info, let me know.

share|improve this question

1 Answer 1

For problems with dropping the table the things I would look at making sure there isn't a trigger, view or foreign key preventing the table from being dropped. Next time you apply the snapshot run Profiler pointed at the target database/server to return only Exceptions and Error messages while applying the snapshot against the subscriber. The messages returned should guide you to the exact cause.

Regarding the snapshot becoming invalid, double check the retention period configured for your distribution database. If this is less than the total time it takes to successfully apply the snapshot to the subscriber it will invalidate the snapshot and re-run the snapshot generation and restart the copy to the subscriber.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.