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.
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?
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.