Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2004

    Unanswered: Transaction log file and DTS

    I made a DTS package to import a bunch of data from text files into a db...and the log file keeps filling up.

    This is a hosted server and I only have 35mb log file.

    I'm really new to the DTS what I set up in the DTS is
    1. FTP 9 text files down into a temp folder
    2. Delete all data in the 9 corresponding tables in the db.
    3. import the data from the text files back into the db.

    My client will need this DTS run daily.
    What are my options for making sure the transaction log doesn't fill up everyday?
    The transactions by the DTS (all the deletes and inserts) don't really need to be logged at all.
    Is there some kind of maintenance plan I or the net admin could set up?
    Is there a SQL command I could add to the DTS to make it not log the transactions that belong to this DTS?
    Is there a better way to setup the DTS?

    Thanks for any help!!!

    Question 2:
    Is there a "notify me if someone posts to this thread" feature anywhere on this site? I didn't see it.

  2. #2
    Join Date
    Jul 2003
    San Antonio, TX
    First, make sure you specify TABLOCK (see checkbox in picture)
    Second, specify the batch size (example shows 1000 rows)

    TABLOCK will speed up the process, while batch size will ensure that CHECKPOINT can take place, thus - prevent a run-away growth of the log. Your db has to be in Simple Recovery mode.
    Attached Thumbnails Attached Thumbnails Transform Data Task Properties.bmp  
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Sep 2003


    If the DB is in full recovery mode, you can run log backups every 10 minutes or more frequent from SQL Agent (till the time processing is done)

    Backup log db_name with truncate_only

    Make sure you backup the full database after processing is over

Posting Permissions

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