I have a job that runs daily that imports a huge text file to my database. I do not want the transaction log to track every transaction log in this DTS. Is there a way to import it using my DTS and have the log ignore it?
you can set the recovery mode to Simple, in which cause no log entries are created, you should do a complete backup after and then set the recovery mode back to where you started.
you can also set the recovery mode to bulk-logged, and follow the same procedure.
I must note JeffRobinson's mistake.
SQL Server's Architecture is more different than oracle.In SQL Server's architecture,u can not avoid transaction log writting(In oracle,u can avoid logging some ddl/dml in some situation).
SQL Server's database has three models,let's see the different between them:
1 Simple model
SQL Server will log any ddl/dml at any time.But will truncate the log at checkpoint autmaticly.The space of truncated log entities can be reused at next log writting.
The consequnces of simple model are:
a) U can avoid the size of log file making rapid progress
b) U can not do any-time recovery,u can only recovery the database to last completed backup.
2 Complete model
SQL Server will log ddl/dml.It will not truncate the log at checkpoint autmaticly.So the log file keep the sequential changing of the database.
The consequnces of Complete model are:
a) U can do any-time recovery if u have a complete backup and all transaction-log backup after that complete backup.
b) if u haven't any backup schedule,u must faced the log file size progress rapidly.
3 HUGE-LOG model
Same as complete model,but when u do bulk-insert,sql server will not produced any log entities.It's only for dba do batch data migration.So when u finished bulk-insert operation,u must do a complete backup.It is requisited,otherwise u must risk of data lossing.
set your database's recovery model to simple mode
import the text file
set the database's recovery model back to whatever it was
Immediately back up your database
Sit back exhale and sip your [rdjabarov] Margarita