Results 1 to 8 of 8
  1. #1
    Join Date
    May 2009
    Posts
    56

    Unanswered: Turn off logging?

    Hey guys, I am doing a very large insert from one table into another in my database. The LOG File actually ends up being 40+ GB. I can't have this. Is there a way to turn off logging for this transaction? Or would using table compression keep the log file from getting so big? I am already using simple recovery model. I am running SQL Server 2008.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Are you actually transferring 40 GB or so of data? Some quick things to look for are how many indexes are on the table you are inserting into? Also, are there triggers on the target table?

  3. #3
    Join Date
    May 2009
    Posts
    56
    Quote Originally Posted by MCrowley View Post
    Are you actually transferring 40 GB or so of data? Some quick things to look for are how many indexes are on the table you are inserting into? Also, are there triggers on the target table?
    No, the table size is about 9GB. There are indexes, and I just added one a couple weeks ago. So I believe that is the culprit. There are no triggers. Could I possible set the insert to not rebuild the indexes right away, keeping the log file small as a result? And then just do a rebuild on the table after the insert?

  4. #4
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Yes, disabling the indexes first and rebuilding them *one at a time* would produce less log. As of now, both the inserts and index updates are logged in one huge transaction. If you disable indexes first, only the inserts (and possibly clustered index/unique indexes (don't disable these, it may jeoperdize integrity)), will be logged first. When this is finished, and the transaction commits, the internal space in the log file (or most of) is freed up, and thus reused when the first index rebuild is issued, and so forth for the following index rebuilds. Everything must be in each own transaction, which is committed before the next start, for this to work.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  5. #5
    Join Date
    May 2009
    Posts
    56
    Thanks, I will try disabling the indexes. Also, would table compression allow for a faster inserts? The insert is heavily IO bound currently. Thanks.

  6. #6
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    Table compression may cause less IO, and thus improved performance. But, keep in mind that it adds additional CPU load.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  7. #7
    Join Date
    May 2009
    Posts
    56
    Quote Originally Posted by roac View Post
    Table compression may cause less IO, and thus improved performance. But, keep in mind that it adds additional CPU load.
    Yes I am aware of that. I am actually going to hold off on using table compression on these tables for now. Disabling the indexes have helped a ton and solved my issue. Thanks for all the help.

  8. #8
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    No problem, happy to help.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

Posting Permissions

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