DTS and Log File

  • Ok, I have a problem that maybe easy to solve because of my inexperience but it sure is baffling. I am trying to load a text file into a table on SQL server. This file is around 635 meg. When I try to load it through a DTS it will run through all the rows (1228000). Then it will hang for a while then I will get a execution failure saying that the log file is full. Then it will tell you to backup the log file and blah blah blah. Problem is I do this and then try again and get the same problem. As a matter of fact the second time I tried it told me the tempdb log file was full. Any ideas? I have the log file for the database set to expand as needed.

    Thanks

    Tom

  • Sounds to me that that there is not enough physical space on your disk to perform the increase.

    Check the space usage of your physical disk and increase as necessary. If you cannot increase the size of disk, then think about halving the file, truncating the log and then continuing with the rest of the file.

     


    ------------------------------
    The Users are always right - when I'm not wrong!

  • DTS usually uses a form of BCP to do the loads.  If you don't care about transaction control, then first remove the indexes on the table and the load operation will then be non-logged (and much faster).  Add the indexes back after the load is complete, though if you have a clustered one you'll need space in your database and you may blow out the log doing that...

     

  • Is the correct way to turncate the log without saving

    Backup LOG <dbname> Truncate_Only

     

  • Tom,

    The correct syntax is BACKUP LOG <dbname> WITH TRUNCATE_ONLY

     

    Greg

    Greg

  • Thanks everyone for your help. I have figured out what was wrong and what to do. So it was an easy solution. Truncate the log and shrink the database was my answer and all worked.

    Thanks for pointing me in the right direction

    Tom

     

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply