September 23, 2009 at 6:14 am
I shall use bulk insert or bcp or SSIS tomorrow for huge bulk insert.
For any bulk operation is this sufficient that I change the recovery model to BULK LOGGED? Can I be ensured that the bulk logging will not happen?
-lk
September 23, 2009 at 6:21 am
luckysql.kinda (9/23/2009)
I shall use bulk insert or bcp or SSIS tomorrow for huge bulk insert.For any bulk operation is this sufficient that I change the recovery model to BULK LOGGED? Can I be ensured that the bulk logging will not happen?
-lk
There will still be some log entries but it will be minimal
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
September 23, 2009 at 7:03 am
just setting recovery mode to bulk logged is not enough to guarantee minimal logging, a number of conditions have to be met:
bcp and bulk insert:
table not replicated
tablock hint used
no clustered index (not true if table empty but if you batch up insert it doesn't STAY empty and switches to full logging)
SSIS I am not 100% sure. guaranteed this will be the slowest method though if its a large amount of data.
be aware of ant constraints and triggers as well.
---------------------------------------------------------------------
September 24, 2009 at 8:39 am
Oh this was the answer george sibbald-364359, I was looking at. Many thanks george sibbald-364359.
September 24, 2009 at 8:42 am
george sibbald-364359, what is the meaning of "tablock hint used"? Do you mean we should not use tablock hint?
-lk
September 24, 2009 at 9:01 am
luckysql.kinda (9/24/2009)
george sibbald-364359, what is the meaning of "tablock hint used"? Do you mean we should not use tablock hint?-lk
other way round, if you want th bulk insert minimally logged you need to use tablock hint
---------------------------------------------------------------------
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply