bcp - Transaction LogFile Size issue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

sriharsha.karagodu@gmail.com - 17 Mar 2008 14:56 GMT

Hi Experts,

I am having new issue again,

My task is to copy data from one table to another table residing in
different database.
Table happenes to be extreamly large. (Contains around 15 million
rows.)

I tried several ways (SQL query, SSIS packages,etc...)
I found BCP utility suits my requirement. So planned for BCP.

I am trying following ways (Two steps)
1. bcp <MyFirstDB.TableName> out <MyFlatFilePath> -n -T
2.bcp <MySecondDB.TableName> in <MyFlatFilePath> -n -T

(Basically, Copying data from source to flat file and from there to
DestinationTable.)

Here problem is, My transaction Log file (MySecondDB_log.ldf) grows
like hell on second command. It grows upto 8 GB (max. free space That
I have on disk :) ).

My datatransfer will be incomplete because of no space on HardDisk.

PLease let me know, where I am going wrong,if you have better method,
how can i optimize my data transfer. (My log file grows by 2 MB, not
with %ge)

Thank you in advance,
Sriharsha Karagodu.

Reply to this Message

Sean - 17 Mar 2008 14:59 GMT

Are you changing the second database's recovery to bulk?

On Mar 17, 9:56 am, sriharsha.karag...@gmail.com wrote:

> Hi Experts,
>
[quoted text clipped - 27 lines]
> Thank you in advance,
> Sriharsha Karagodu.

Reply to this Message

sriharsha.karagodu@gmail.com - 17 Mar 2008 15:08 GMT

> Are you changing the second database's recovery to bulk?
>
[quoted text clipped - 33 lines]
>
> - Show quoted text -

Actually I read about Changing the Recovery Property. But Where Do I
Get that option?
When I Do Property of Database--> options-->recovery, This will have
three options like,
TonrnPageDetection,
CheckSum,
None.
So, Not sure where will get option to change the recovery to BULK.

please guide me.

Reply to this Message

Sean - 17 Mar 2008 15:16 GMT

You can do it visually, but the syntax goes like this:

ALTER DATABASE [database name]
SET RECOVERY [either: FULL | BULK_LOGGED | SIMPLE]

Also, to see what the current recovery model is run: SP_HELPDB
[database name]

1. Run SP_HELPDB [database name], and note the model used.
2. ALTER DATABASE [database name] SET RECOVERY BULK_LOGGED
3. Run SP_HELPDB [database name] to double check the settings
4. Run bcp import
5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
6. SP_HELPDB [database name] to double check

Reply to this Message

Russell Fields - 17 Mar 2008 15:25 GMT

Shriharsha,

When BCPing in so much data, it is also good to use the batch size operator
to control the transaction size. Such as:

bcp .... -b 50000

This will break up your bcp into about 300 batches, which will speed it up
and gives you more transaction log control. You could then (if necessary)
run extra BACKUP LOGs during the bcp in.

RLF

> You can do it visually, but the syntax goes like this:
>
[quoted text clipped - 10 lines]
> 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> 6. SP_HELPDB [database name] to double check

Reply to this Message

Harsha - 18 Mar 2008 08:12 GMT

Thanks Rusell and Sean,

I have changed the model to "BULK_LOGGED" and used -b attribute in bcp import.

Smaller the batch size, faster is the data transfer

> Shriharsha,
>
[quoted text clipped - 23 lines]
> > 5. ALTER DATABASE [databasename] SET RECOVERY (output of step 2)
> > 6. SP_HELPDB [database name] to double check

Reply to this Message