John W. Colby
jwcolby at colbyconsulting.com
Wed Aug 25 06:19:07 CDT 2004
I looked at Shrink which will reduce the current size by 30% according to EM. It appears that the extra space is there for future expansion and seeing as I have a slew more files to import there is no point in doing so YET. I will keep you in mind for those hard questions about largish DBs! John W. Colby www.ColbyConsulting.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of JMoss Sent: Wednesday, August 25, 2004 6:39 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] Big db update John, Have you used Enterprise Manager's SHRINK on the DB and log file? Also, have you a dedup process for this monstrosity? I was heavily involved in building large databases a lot like you're currently doing, and about 10 -15 % of what I was loading was dups. Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Wednesday, August 25, 2004 12:11 AM To: dba-sqlserver at databaseadvisors.com Subject: [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 _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com _______________________________________________ dba-SQLServer mailing list dba-SQLServer at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/dba-sqlserver http://www.databaseadvisors.com