0

We have several tables which we "refresh" frequently by rebuilding them in a staging-table, then performing a metadata-switch to the production-table using the ALTER TABLE <source> SWITCH INTO <target> statement.

These operations are causing a huge amount of logging, and we're not sure how to handle it. For now we just moved these tables and operations to a new database using the SIMPLE recovery model, but I would love to hear alternative solutions to this problem.

How do you suggest we handle the massive log generation as a result of frequent table rebuilds?

2
  • Why does the table require frequent rebuilds? What is the nature of the data it contains? How is it used? Jul 29, 2016 at 8:27
  • It joins peripheral information from 3-4 different tables, and the amount of CASE'ing and function usage on columns makes too heavy for, say, an indexed view. The generated table itself is only used for reading, but it is under sufficiently heavy to justify the need for indexes.
    – krystah
    Jul 29, 2016 at 8:53

1 Answer 1

1

one option (if feasible for you considering the downside) would be to change the database to bulk logging and bcp into the target table from a view reading the source tables. the actual logging should be minimized, but the log backup will still be huge as it will include the changed extents. a downside of bulk logging is that is compromises the ability to point in time recover to a time frame during the bulk logging operation. make sure you take a log backup immediately after the bulk import

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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