Results 1 to 5 of 5
  1. #1
    Join Date
    May 2003
    Posts
    41

    Unanswered: Shutting off Transaction Log on DTS

    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?

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    No.

    -PatP

  3. #3
    Join Date
    May 2004
    Posts
    6

    Transaction Log

    If you are talking about the SQL Transaction,

    Yes, but caution should be taken,

    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.

  4. #4
    Join Date
    Jan 2004
    Location
    Shanghai,China
    Posts
    76
    Quote Originally Posted by JeffRobinson
    If you are talking about the SQL Transaction,

    Yes, but caution should be taken,

    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.

  5. #5
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    Simple
    BulkLogged
    Full

    ??????????

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •