I need to create a couple of snapshot tables that will export a large amount of data. While running a few tests, I need to create the table, import data, inspect the results, update the export query and then delete all the data in the table and start over.

The table, when finished, will be moderately large but dropping and reloading the data again and again is making the transaction log file MASSIVE.

What I would like to do is cut off transaction logging for these files until I'm satisfied with my results, or clear the transaction log for the table. To be clear, I do not want to cut off logging for the entire database. This is a shared, developer DB and we need to keep the log running.

I need to create a snapshot process that pulls certain data from a massive audit table on our production server. I want to move the pertinent data to our dev server, and create a process that can run nightly/weekly to get the most recent changes. However, I need to do an initial population from the full audit table. I'm new to the company and I do not know anything about their data structures. Thus, I need to pull the data, do a bit of analysis and then fine-tune my queries.

Can this be done? I've tried to Google this multiple ways and multiple ways but I can't find anything related to table-specific transaction log settings.

  • 1
    This isn't possible on a per-table basis. Maybe you could copy the specific tables into a new database which is in simple mode? If that's not possible look into bulk-logged mode msdn.microsoft.com/en-gb/library/ms189275.aspx – dwjv Sep 11 '15 at 16:06
  • 2
    @dwjv simple recovery doesn't "not log"... – Aaron Bertrand Sep 11 '15 at 16:07
  • how about trying to make the transactions for the imports, updates, etc. smaller batch sizes? I assume the DEV DB is already in simple recovery mode? – Pimp Juice IT Sep 11 '15 at 16:15
  • what's your recovery model? – swasheck Sep 11 '15 at 16:34
  • 1
    @RLH You may want to look into this type of approach: Part 1 | Part 2 – Aaron Bertrand Sep 11 '15 at 16:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.