How to reload a table without growing the log file?

Question

I have the following scenario which runs every night using SSIS in SQL Server 2008:

• create a few temp. tables, and import data from an ODBC data source
• when the imports are complete, drop existing tables, and rename the temp. tables to the same name as the dropped tables

What do I need to do in either my SSIS job or SQL Server settings to minimize the logging? I don't want to turn off logging for the whole db, since there are only these few tables that get updated nightly.

Answer 1

Unfortunately logging is a database level setting. Perhaps you could do the load into a separate DB (with SIMPLE or BULK LOGGED) and then, presuming that bit is successful, UPDATE your existing tables.

Answer 2

Is there a possibility of splitting these tables out into another database?

You would be able to manage not only the logging settings separately, but the logs and backups themselves and thus you could control it with much more granularity.

You could create views in the old database which point to the tables you move to the new database, so code might not even need to change much.

Answer 3

More frequent backups to transaction log can keep up the size and not demanding for increase in log size. Also I see that storage is cheap now a days where you can relocate your LDF file to that drive for ease of free space.

Answer 4

I would think that changing the recovery model to be bulk-logged immediately prior to the table loads then switching back to full would be the best option. Be sure to read up on things to consider (http://msdn.microsoft.com/en-us/library/ms190203.aspx) before implementation. This can be accomplished via ALTER DATABASE statements that could be added to the control flow of your SSIS package:

-- switch to bulk-logged mode
alter database <dbname,,> set recovery bulk_logged;

-- switch back to full
alter database <dbname,,> set recovery full;

Though I also have (and currently) relied on more frequent log backups to keep the log tamed, this is more art than science. Like most art, something's bound to surprise you; unfortunately, these surprises are more like a visit to the dentist: painful and probably preventable :).

I would also wonder if you're using the fast-load option on the oledb destination component (presuming that's what you're using). This should be minimally logging as well. There's a good white paper on numerous techniques for performance when loading data that might be worth checking out as well (be prepared, it's a doozey) . . . perhaps it can give you a few more ideas!

Answer 5

I would put these tables in another db with simple recovery enabled, and then use a synonym in the other database to make them appear local.

This will let you leverage the simple recovery model and fast load option recommended by others for these tables.