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 |
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
 Posted - 2011-07-29 : 12:53:03
|
| Hi,I have tables X, Y and Z in database. I have to put the result obtained from X and Y into table Z.Currently, I have an SP that returns the result of the X and Y to C# application. This result will be copied into table Z using "System.Data.SqlClient.SqlBulkCopy". The advantage is that during the insert using bulkcopy, log files are not created. When I use System.Data.SqlClient.SqlBulkCopy, the operation is getting completed in 3 minutes; in normal insert it takes 30 minutes... This particular operation need not be recovered; however other operations in the database has to be recovered - hence I cannot change the recovery mode of the whole database.I want to avoid this extra round trip, by handling the insert in the stored procedure itself. However, it should not be using any log files. How to achieve this?Note: Since System.Data.SqlClient.SqlBulkCopy can do it, we also should be able to do it, right? Volume Of Data: 150,000Database : SQL Server 2005Note about normal insert: I was getting the exception "Insufficient system memory in resource pool 'default' to run this query" when i was running it as a single insert. To revolve this, I spitted it and used a loop which will run 5 times. Though it was completing the operation, it was taking 30 mins to complete. (This long time happens only in one DB server; when I tested with a different DB server it is completed in less than 8 minutes)Note: I am the only person accessing db for the time being; this is the only operation happening. So I don’t see a chance of blocking.ThanksLijo Cheeran Joseph |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-29 : 13:56:02
|
| System.Data.SqlClient.SqlBulkCopy is NOT an unlogged operation. At best (bulk-logged or simple recovery) it's a minimally logged operation.Were there such a thing as an unlogged operation and it failed, the entire database would have to be marked as suspect and inconsistent, requiring a restore from backup. I'm sure you can see why there's no such thing allowed.--Gail ShawSQL Server MVP |
 |
|
|
Lijo Cheeran Joseph
Posting Yak Master
123 Posts |
Posted - 2011-07-30 : 03:56:44
|
quote:
System.Data.SqlClient.SqlBulkCopy is NOT an unlogged operation. At best (bulk-logged or simple recovery) it's a minimally logged operation.
Okay. How do I achieve the same speed from the stored procedure itself?Note: This operation should not mark the database as suspect/inconsistent, even if it fails.Note: This is a zip code insert operation (after reading from an uploaded new csv file); even if it fails in between, there is no need to recover (i.e., remove already inserted data). ThanksLijo |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-07-30 : 06:46:03
|
| Bulk insert? BCP?You can't play with SQL's recovery process. If an operation fails it WILL be rolled back in its entirety. It has to be.--Gail ShawSQL Server MVP |
|
|
|
jeffw8713
Aged Yak Warrior
819 Posts |
Posted - 2011-07-30 : 11:19:13
|
| You could use SSIS instead of your custom C# program - basically does the same thing. |
|
|
|
suba.anand
Starting Member
8 Posts |
Posted - 2011-08-01 : 06:11:49
|
| try setting the recovery model to full logged. that might help you. |
|
|
|
GilaMonster
Master Smack Fu Yak Hacker
4507 Posts |
Posted - 2011-08-01 : 09:39:45
|
quote:
Originally posted by suba.anand
try setting the recovery model to full logged. that might help you.
Err, so to get an insert to minimally logged you suggest changing to a recovery model where everything is fully logged?--Gail ShawSQL Server MVP |
|
|
|
|
|
|
|
|
Insert without Logging