June 20, 2008 at 10:52 am
I am copying several tables from a DB2 database into a a SQL Server 2005 database. The transaction log fills up quite fast with approximately 500k rows among all tables every day.
I've looked at BULK INSERT and OPENROWSET(BULK..) but those both want to use data files.
Is it possible to use a minimally logged BULK INSERT operation of some sort and query the source table directly without exporting the source table to a data file first?
June 20, 2008 at 11:55 am
you won't get minimal logging if the destination table is not empty unless you use TABLOCK and there is only a single clustered index on it. The transaction log size has to be managed through Transaction log backups.
If you insert directly from the linked server a distributed transaction will be opened and that will take probably locks for a long time too.
You could create temporary tables with SELECT INTO though but I am not sure that is what you want.
* Noel
June 20, 2008 at 12:48 pm
You might find that you're better off exporting to a file from the DB2 database and then bulk importing (minimal logging).
SSIS can do that for you pretty easily.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
June 20, 2008 at 1:19 pm
noeld (6/20/2008)
you won't get minimal logging if the destination table is not empty unless you use TABLOCK and there is only a single clustered index on it. The transaction log size has to be managed through Transaction log backups.If you insert directly from the linked server a distributed transaction will be opened and that will take probably locks for a long time too.
You could create temporary tables with SELECT INTO though but I am not sure that is what you want.
The source database is a report writer copy of a live database. I'm copying tables to the destination database in SQL Server for web app performance reasons (the host system places low priority on that RW database and running anything against it is slow). Since this "copy" doesn't host live data, and is recreated every day, is it possible to turn transactions off altogether?
June 20, 2008 at 2:31 pm
You can't completely eliminate transactions (so far as I know). Exporting to a file and bulk importing would result in very minimal loggings (under most settings). Like, it might expand your log file by a few kilobytes each time. (Not each row, each time you import.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply