January 7, 2003 at 11:57 am
I was wondering if anyone had a good article on what non-logged operations are available in SQL. I am moving around millions of rows of data that I have copies of in external files. I have no need to log anything so I would like to rewrite somethings and use the non-logged operational logic.
January 7, 2003 at 2:42 pm
I guess that is more of a misconception than not. It is actually minimal logging that occurrs on some things such as Truncate Table and depending on your Recover Model (SQL 2000) or some DB options in SQL 7 BULK INSERTS, BCP and SELECT INTO operations are not logged unless they are set to. The transaction logs control the flow of data so that a database cannot be easily corrupted by tracking changes. You can however set it to remove the tracked changes once complete (Truncate on Check Point or Simple Recovery Model). So in truth you can only DELETE all records (TRUNCATE TABLE) or INSERT in large bulks of data (SELECT INTO, BULK INSERT, bcp utility) to minimalize TL growth depending on settings and options used, or UPDATES/DELETES/INSERTS fully logged you can set db to truncate the TL when operation complete.
January 7, 2003 at 5:07 pm
I agree strongly with antares686.
What is it your really trying to accomplish? Just speeding up your imports?
January 8, 2003 at 7:53 am
I am alright with the speed. The fact is I have two tables right that have over 15 millions rows in each. I don't want the tranascaiton being logged because my log grows out of control and I don't have any space for it. I could be wrong but I don't think SELECT INTO allows you to append to another table. I just want to append to another table with minimal strain on the TL. Maybe I should look into bcp/bulk insert.
January 8, 2003 at 8:13 am
If you can use BCP that would be an excellent choice.
If you need to do allot of data manipulation then control the data load through transactions. Sounds like your trying to load 100K plus records into a table in one shot. Even with trunc. log on checkpoint and select into / bulk copy set to true the transaction log is still be written to. It just won't stay in the transaction log once you have committed your changes.
Break the load into smaller batches by use an internal counter once you hit your threshold commit the transactions and rest the counter.
John Zacharkan
John Zacharkan
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply