GSquared (3/8/2011)
Koen Verbeeck (3/8/2011)
In the somewhat special case of ETL, I'd sometimes like to turn of logging.If a data load fails, the destination table can be truncated and the load can start over again, so you would not have to worry about inconsistency.
I'm only talking about the import layer here (the E of ETL). If updates are performed on datasets in the database, I would very much like logging, as I would like to go back to a previous state if necessary. But for imports, nah, I don't need logging 🙂
Would the Bulk Logged recovery model accomplish what you want on that?
You can also have a staging database, where you bulk import, et al, kept in Simple recovery, and just leave it out of the backup and maintenance plans. The log will grow to accommodate your imports, but it's simpler and less critical than a "real" database. If needed/wanted, keep that one on a cheap RAID 0 array. If it crashes and burns, replace the disks and re-run the create script from source control, and don't worry about recovery. Just make sure it's set up so that you don't lose anything that matters if you lose the whole database.
Bulk Logged recovery model certainly is an option. So is the Simple recovery model.
My point is that when the ETL tightly controls the batch process and the destination database is only used as a "dump" for the data (aka volatile staging area, where destination tables are cleared before the import process), that all the logging is just extra overhead interfering with (BULK) INSERT performance. For the same reason it is also recommended not to have constraints (be it foreign keys or check constraints) and to minimize indexing (you can even drop the indexes and recreate them after the import process).
If it is a non-volatile staging area, then a backup before the import process is sufficant, as Lynn already mentioned.
But maybe I'm preaching to the choir 🙂
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP