March 12, 2009 at 10:58 am
I have a problem and was wondering, can someone please take a moment to answer:
I have a SSIS package all what it does is move million of rows from X database to Y database. X database is residing on Server A and Y database on Server B. Recovery on Y database is set to simple - but the log still grows.
Additionally i have set the sp_dboption 'reports','select into/bulkcopy', true as well for Y database. but no luck.
Is there a way, i could avoid this transaction log growth?
appreciated any input.
Many thanks in advance.
March 12, 2009 at 3:19 pm
Simple recovery mode logs everything that bulk-logged mode would, the difference is that it doesn't require a log backup to free space for reuse. The log file may still need to grow if you have long-running transactions as those log entries cannot be cleared until committed in case a rollback is needed. This may be the problem that you are hitting.
We would need to see some specifics of how the data is being copied to give you more ideas.
March 12, 2009 at 4:27 pm
thanks for the answer.
to answer, how the data is copied. it's basically doing like a full copy of one table from X database to Y database through SSIS package. sure the rows are in millions.
isnt there any way, i avoid this txn log from growing?
March 12, 2009 at 4:36 pm
there will always be log usage, all you can do is minimize it by batching up the copy into smaller transactions.
Once the log grows the first time the fact that it is large is not in itself a problem (especially as you are not backing it up), so if drive space is not an issue you could leave it.
---------------------------------------------------------------------
March 12, 2009 at 5:25 pm
thanks for the answer.
March 12, 2009 at 9:59 pm
I don't know if this will add anything for you - but it helped me when I figured this out. The reason why there is always log activity is that anything involving data changes gets logged FIRST and then the changes occur (so that it can meet the Atomicity rule about RDBMS'es: everything goes in or nothing does). So logging always occurs, whether full or minimal.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply