[dba-SQLServer] Big db update

As you know by now I am working on a largish database, 65 million names /
addresses plus demographics, ~600 fields it turns out.  Over the last
weekend I tried working on it using my P64 laptop with 512mb RAM.  I bought
a pair of 200gb disks which I will end up putting in my server but for now I
took one and put it in an external enclosure and hooked it to the laptop
over a usb2.0 port.  I then started trying to import the database using dts.


The raw data is broken down into zip files (PK zip) each containing 3
million records.  The imports worked but as I went along, the times to
import kept getting longer and longer.  Eventually file 5 failed to import.

It turned out that the database file was fragmented into 179 THOUSAND
fragments!!!!!  Defrag wouldn't work.  The database file by this time was up
around 80 gbytes.  I went down to Wal-Mart and bought another (Seagate)
120gb external drive and hooked that up to another usb port.  By copying the
80g file from the first drive to the new drive, it defragged.  I then tried
to import again.  File 5 went in but file 6 failed.  Again, the file was
fragmented into ONLY about 140 THOUSAND fragments.  No amount of coaxing on
my part would allow the 6th file to import.  I even figured out how to use
BCP but that also failed.  Tempdb read failure, which BTW was out on my C:
drive.

Anyway I just gave up at that point figuring that 512 mb of RAM was simply
insufficient.  I had ordered a pair of 1g sticks of ram, so tonight when I
got home from the "vacation" I threw them in the server and went back to
work.  

I had done the import of files 1 and 2 on my server and then moved them to
the external enclosure to take to Syracuse on "vacation".  Tonight I
installed the ram in my desktop and used BCP to import the 3rd, 4th and
(currently working on) 5th files.  So far it is as smooth as silk.  The
database resizes as needed but so far the database file is at 91g and only 3
fragments, and that is due I believe to having to fit around existing files
out on the disk.  So far, MUCH better.

The SQL Server piece grabs 1.9gb of ram for itself.  There is no sign of
swap file activity which was just a killer on the laptop.  With only 512mb
of ram the poor laptop was just beating up the swap file.  I tried moving it
out to the external disks but the laptop refused to boot that way so I had
to keep it on the C: drive.  With 2g RAM there is no swap file activity at
all, even though all of the ram is used by SQL Server.  I am doing the BCP
directly on the server using Query Analyzer, with the 10g (decompressed)
text "source" files on one drive and only the database on the 200g disk.
SQL Server pegs the CPU needle, 100% usage and about 95% memory usage.

I understand that I could set up my various other machines to each run BCP
simultaneously against the SQL Server instance running on the server, but it
turns out that the client really wants the file reconstructed in the order
of the text files so that's a no go.  It appears however that I may actually
get my poor underpowered 2.5g AMD Barton with 2g RAM to work this file.  We
shall have to wait and see whether I get all 65 million records in 200gb of
hard disk.  It is looking like I won't since I am on the 5th file and the db
is already up around 90g with 17 more files to go!!!  At this rate, I am
looking at MORE THAN 400gb.  

On my laptop I actually compressed the disk which worked very well actually
(other than the extra overhead in compression).  I seemed to be getting
about 2:1 compression of the database file.  This thing is getting out of
hand I must say.

Anyway, I just thought you folks might like a little update on the trials
and tribulations I've been going through trying to get this working.

John W. Colby
www.ColbyConsulting.com