Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
 Posted - 2004-09-01 : 08:08:46
|
| I have a production server and I just need to keep a couple of months data. What I'm doing now is running a scheduled DTS every first day of the month to backup the previous months that needs to be dumped on another server then delete this data on the production server. Estimated row size is 20 millionWhich is better? DTS or Data Export?Also, how do i force commit transaction in DTS? I observe, it's running all the transactions as a batch, so when I get an error due to network problem, the entire batch gets rolled back.Or should I just stick to DTS and divide the rows? |
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-09-02 : 06:09:20
|
| If you're exporting 20 million rows then network drops can be an issue. It helps if it's a gigabit switch, so that's something to look at depending on your infrastructure.On your transfer in DTS, look at the options tab for the data pump, the insert batch size determins how often it will commit. Beware though of increased run times if you set this value and have indexes on your destination, it will rebuild the index each time, and the time can get pretty big.-------Moo. :) |
 |
|
|
jen
Master Smack Fu Yak Hacker
4110 Posts |
Posted - 2004-09-02 : 23:36:37
|
| Thanks for the input Moo (do you have another alias? lolz)I do have indexes on destination.Hmm.. actually this 20 million will still grow. So do you think I just schedule the process for several days and divide the rows? It's pretty much straightforward coz the table has a datetime column.I'm not on good terms with our TSG, they won't appreciate a request for a gigabit switch. lolz, anyways, i'll try the plan first then if all else fails, i'll just muster enough courage to make the request. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2004-09-03 : 03:26:03
|
| Maybe extract in smaller batches into a staging table with no indexes, then once you have all your rows copy that table into your proper archive table. Or just rename it as month_whatever and index.<-- Aliases for me. ;)-------Moo. :) |
|
|
|
|
|
|
|
|
DTS or Data Export