Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
JP
Starting Member
6 Posts |
 Posted - 2004-07-07 : 15:04:15
|
| How can I turn off logging when performing an insert (append) statement?I have three large tables (>3Gb each), loaded using Bulk Insert with Recovery set to Bulk_Logged. The three are text extract sections of the same table from another database (Oracle) split and written to DVD. When I run the bulk insert, logging is minimal. However, I can't find a way to put them all into one big table without the transaction log file becoming ridiculously huge, larger than the data file. I've searched forums and sites, and haven't found a similar question or problem.Any help is appreciated. Thanks.hopelessly lost |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-07 : 15:44:02
|
| You can't turn off logging. One option would be to do the inserts in batches with SET ROWCOUNT. In between batches, perform a transaction log backup.Tara |
 |
|
|
JP
Starting Member
6 Posts |
Posted - 2004-07-07 : 15:58:07
|
| Thanks for the quick reply. At least now I know that logging isn't the option I thought it was. Another question which I haven't been able to understand: in all the documentation I've read, it states that backing up the log removes the committed transactions. Yet when I look at the size of the log files, it's no different (size has not decreased). How do you get the backup to release the unused space?Thanks.hopelessly lost |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-07-07 : 16:13:03
|
| The size may not decrease, but there is a lot of free space in it. To see what I am referring to, left click on your database in Enterprise Manager, then right click, click view, then click Taskpad. A visual representation of the files will come up. You'll notice the free space (in blue) in both files. After the transaction log backup runs, free space will be increased. You can shrink the log if you want with DBCC SHRINKFILE. I don't recommend it though unless you know it will never need that space again. The reason being is that you get a performance hit if it needs to grow.Tara |
|
|
|
JP
Starting Member
6 Posts |
Posted - 2004-07-07 : 17:06:27
|
| Thanks again. I'm not sure if the Set Rowcount will work. Each of the section tables has about 15M rows and I don't know if you can set the starting position, say using increments of 10-100K rows, then run the backup after each execution. Just knowing more about the log file is enough. Also, thanks for the Taskpad view of the allocations. Since my database is a read-only one, maybe after all the data is loaded, I might consider running the Shrinkfile command.hopelessly lost |
|
|
|
|
|
|
|
|
Non-Logged Insert Transactions