Results 1 to 4 of 4
  1. #1
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Exclamation Unanswered: SQL Server importing

    I have a problem with SQL Server 2000. I have a rather large database (3 gigs). I have the recovery mode set to bulk-logged because I'm importing 300,000+ records (through Acccess (linked tables) due to several importing checks which need to be done which SQL Server DTS can't handle.) The problem is that my Transaction log keeps growing very large after each import. I've tried several different settings in SQL Server but I don't want to slow down the import process which runs through a rather intensive checking process. The problem is that the Transaction log keeps growing and will only shrink after nightly backups.

    I've tried shrinking the database after 1 of the imports (which causes the Transaction log to grow to 400+ megs) and can't get it to shrink no matter how I do it (even tried using Backup Log Rebates WITH Truncate_Only as well as the All Tasks - Shrink Database.)

    I also tried setting the Transaction log to 400 megs and not allowing it to grow.

    What's the best way to do this with the settings in SQL Server? I still need to use Access to do the checks (a stored procedure/DTS won't work with all the checking needing to be done on each import.)

  2. #2
    Join Date
    Feb 2002
    Location
    Houston, TX
    Posts
    809
    If you don't want the transaction log to fill up why not export your data from Access and then use BCP or Bulk Insert to load the data?

    Using Bulk-Logged recovery prevents logging in only four operations, Select Into, BCP/Bulk Insert, Create Index and text and imiage operation. Check out Bulk-Logged Recovery model in BOL.
    Paul Young
    (Knowledge is power! Get some!)

  3. #3
    Join Date
    Dec 2004
    Location
    CA, USA
    Posts
    63

    don't fight it

    That's the nature of the beast. You can only truncate/shrink the t-log after it's been backed up. This is by design for the protection of the data. Disk space is cheap. Get more.

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926

    Shrinking difficult TranLog files in SQL Server

    I found if you have a TranLog file in SQL Server which you try and try but can't shrink, under the Options tab (properties), try setting the recover mode from Bulk-logged to simple (or vice versa), click ok, then switch it back and then run the ShrinkDatabase from the gui.

    This so far has worked for me and has been the only way I can shrink a stubborn tranlog file which won't shrink no matter what commands I try.

    Any comments would be greatly appreciated. I tried running every command there is though.

Posting Permissions

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