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 |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2010-11-19 : 10:18:43
|
| I am importing 120 million rows into a table using BCP.It's taking quite a bit of time to do this. I have put the database in simple recovery mode.Will I get any benefit from switching the recovery model to Bulk Logged? Or is simple the most efficient method.Recovery to a point in time is not important, only performance is.Thanks |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2010-11-19 : 10:28:20
|
| Are you taking a table lock?Set the batch size?Don't think there's much difference between simple and bulk logged - might get interference from simple as the tr log is released.Also with simple there were long pauses during inserts (this was a while ago haven't tested recently) - I assumed it was tr log related but seemed very long. DIdn't happen with logging on.Try testing - remember you need to take a full backup after changing the mode otherwise it's still simple.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2010-11-19 : 12:19:00
|
| Operations that can be minimally logged are minimally logged in both simple and bulk-logged recovery. By switching to bulk logged you'll be adding the need to take log backups--Gail ShawSQL Server MVP |
|
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2010-11-21 : 16:34:19
|
| did you remove the clustered indexes or pks before bcp?did you bcp smaller batches?--------------------keeping it simple... |
|
|
|
|
|
|
Bulk Logged vs Simple Mode for BCP