what is the Best way to pass 40 gb table to another database

[yordangs]

What is the best way to pass values between two databases

Here its my scenario I have a database with a table with 40 gigas I need to pass that table to another database who is in the same server I try the last Saturday do it the next thing from a query console I make the next query


INSERT INTO databaseA..tb_be_gatewaylog , Desc (log_applicationNumber,log_serviceNumber)
SELECT log_applicationNumber,log_serviceNumber, Desc
From databaseB..tb_be_gatewaylog

But after one hour and a half its send me the next error

Msg 9002, Level 17, State 4 The transaction log for database ' databaseA ' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases

I stop trying to pass de data that day; anyone knows how to pass big data into databases in the same server? Don’t mater is isn’t the fastest way

A DTS could be the best way?

Any help will be appreciated, thanks any way

 

[gmmastros]

You should do your inserts in batches.

faq183-3141



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[yordangs]

i will try it, thanks a lot for the answer, this issue i have to solve today thanks

 

[Emtucifor]

If your database is in full recovery mode then batches won't completely solve your t-log problem unless regular log backups are occurring during your load process (t-log, differential, or full, each can shrink your t-log depending on the oldest open transaction at the time of backup).

If your database is set to simple recovery, then a series of smaller inserts instead of one big one will prevent your t-log from growing a lot in the first place. It will still grow to the size of the biggest set of overlapping open transactions, so make sure no process is holding even tiny transactions open for a long period.

If simple recovery is not an option, then there may be a bulk or minimally logged way to insert as well, though your t-log will still grow. I'm not sure on this one. I think bulk logging requires the table be empty to start with? I'd have to look it up to get all the details on it. Bulk operations are still logged, but the amount of data logged is less.

Finally, if the t-log filled up because it was not on autogrow (which is not necessarily a bad thing--don't go changing it without research) and you have disk space to spare, then you can manually increase the t-log size. But work with your dba if you have one, and in any case try to study up before doing anything that's new. If your database is truly in full recovery because it needs to be, and frequent t-log backups aren't occurring, then your data is in danger and something needs to be done about it.

In no case should you be setting your database to simple recovery from full recovery without understanding all the implications of this.