Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146

    Unanswered: large table loads & transaction log issues

    I have a table thatís about 3 gigs, using this table and a few others Iím making another table. The problem is when making the new table my transaction log inflates so much that Iím running out of disk space. What I can I do to prevent this or to keep the transaction log size under control?

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    You need to either try SELECT * INTO new_table FROM old_table, or BCP out from the old table and BULK INSERT/BCP into the new one. When doing BCP IN/BULK INSERT make sure to specify BATCH SIZE, something like 10,000. Also use WITH TABLOCK while doing BULK INSERT.

  3. #3
    Join Date
    Feb 2004
    Location
    PAKISTAN
    Posts
    106
    Hi there, one can also choose Bulk logged Recovery model for the database, BCPs and SELECT INTOs are minimally logged. Ur log file would not escalate rappidly in this case.

  4. #4
    Join Date
    Jan 2004
    Location
    austin
    Posts
    146
    would this work, can the from be a view?

    Truncate table LATEST_VERSION_SERVICES

    BULK INSERT LOG.[LATEST_VERSION_SERVICES]
    FROM v_tbl_latest_version_services

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    No, but you can BCP OUT from a view and then BULK INSERT FROM 'the_file_that_you_got_from_BCP_OUT_step'

Posting Permissions

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