Disable transaction logging?

[philsky]

There's a table in a database that is used to gather data for export to a flat file. This table is always emptied and repopulated each time the process is run. There will never be a need to restore these transactions from the transaction log, and the process causes the transaction log file to become HUGE! I have regularly shrunk this file, but would like to disable transaction logging for this process altogether. Is that possible?

Thanks!

Phil

 

[tlbroadbent]

Transaction logging applies to the whole database, not individual tables. You can't selectively turn off logging on a table. You can never fully turn off logging because the transaction log is not only used for restoring data but for committing or rolling back transactions.

You can do log backups at various points in the process to remove the inactive portions of the log. You could also truncate the log at certain points in the process and then do a full database backup at the conclusion of the process.

If you do large numbers of inserts into the table, the log may grow large even with truncations because truncating a log only removes inactive records. While transactions are active, the records cannot be truncated. You should do inserts in batches with transaction commits to keep the log from growing large.

Have you considered creating a separate database to hold the export table? You could then set the database option to truncate log on checkpoint. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.