Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2002

    Unanswered: Large BCP Problem

    OS - Window 2003 Standard
    SQL - 2000 Standard SP4
    2 CPU 4 GB Memory

    We have a table this is approximate 155 GB with 450 million rows. 90 days worth of data and normalized pretty tight. Obviously this beast is too much to handle (reporting, maintenance) so we are partitioning the data off into weekly tables. I will never need to store more than 90 days worth of data so at the end of each week, we will truncate the oldest week's table and change the constraint on it to handle the next week. Anyway, We have done this and it is working well in 2 testing environment with a lot less data.

    I need to migrate the data from the 1 table 450 million rows to the 13 weekly tables that will approximately make up the 90 days worth of data. I hoped to BCP out the data and Bulk Insert (fast mode). Even 1 weeks (Approximate 35 million rows) goes forever and and don't even see it start to output. I have changed the batch to be larger. Is there a better/faster way to do this or am I just going to have to wait. I'm open to any suggestions.

    Note: Hardware is all Raid 1+0, reading from 1 array and writing to a different array. Performance just seems to slow but I'm not sure what to expect from something this big.


  2. #2
    Join Date
    Sep 2005
    I would be interested in the issues you have with reporting on this database. Usually I am able to avoid having to archive data even with a large database like this.

    With that said, I have another option to offer. You can create a database that has the same table structures as your production database, but with no data. Then copy your old data to this database.


    Insert into archiveDB.dbo.mytable
    select * from productionDB.dbo.mytable

    Be sure and set the recovery model for the archiveDB to simple, so that transaction logs don't grow.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts