April 1, 2004 at 9:58 am
Hello expert,
Hope someone can help me. I am restructing a table with 13 millions records. So I rename the table A to A_bk then recreate table A with new structure. Now I insert the A_bk back to A table. The insert took a few hours. Do any know if their is a way to turn off logging in SQL Server so it will be much faster? I notice my log file grewth to 8 Gigs. I know I can break the insert to one million records at a time but it would not increase performance, it would only reduce the log for growing to big since I switch my database to simple mode. Thank you for you help in advance.
April 1, 2004 at 11:03 am
Logging cannot be turned off if you're using an INSERT command. If, however, you were to use another mechanism, such as BULK INSERT or bcp, you can take steps to minimize logging. However, both depend on exporting the data to a data file first.
K. Brian Kelley
@kbriankelley
April 1, 2004 at 12:52 pm
Thank you, I guest there is not much I can do aside from moving the log file to another drive or faster drive.
April 1, 2004 at 1:00 pm
Brian Knight or another DTS guru might better be able to answer this, but I think you may be able to get a similar operation using DTS.
K. Brian Kelley
@kbriankelley
April 2, 2004 at 12:31 pm
Once you rename the source table A to A_bk, do not create a new table A yet. Just say
Select * Into A From A_bk.
This will create a table A (without indexes ) and copies data into it. Once it is done just add indexes, triggers etc. This method does not use log, as long as the DB recovery model is simple.
April 2, 2004 at 7:43 pm
Another option is to use the Import/Export utility and when you are selecting the methods use the bottom radial (don't have this in front of me right now) I seem to remember that that option does minimal logging as well.
If you REALLY need to do this with little/no logging and are doing this in DEV/TEST/STAGING you can simply change the model of the DB to simple perform the data load and then change it back to whatever it was previously
Good Hunting!
AJ Ahrens
webmaster@kritter.net
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply