Erwin Craps - IT Helps
Erwin.Craps at ithelps.be
Wed Aug 25 02:40:08 CDT 2004
I'm not an SQL specialist but I would like to add my comment if I may? 1) Putting a database on a external disk (USB or Firewire) is not a smart thing to do. The speed of USB/Firewire is so slow compared to regular ATA. But as I read this is an intermediate solution. I'm not sure if SATA external drives already exists but they are much faster and don't use CPU processing power like USB. I noticed to that some tools do not work on external USB drives. Not sure for defrag but anyway that would take ages to do due to low speed. 2) From what I know you can set the initial size of a SQL database and log file to any size you want. So if you estimate to have 3 GB of data, set the initial size of the DB and log to 3GB. The file will be created in one shot, so it will not defragment unless the size grows above 3 GB. Secondly your addditions will go faster. In your old situation SQL server adds 10% of free space when the DB is to small. What you experianced now is that SQL adds some records, expands the database by x%, adds some records, expands the DB by x%, add some records... And so on. This creates the defragmantation. If the size is to big at the end of your additions there is a way to shrink the file again. 3) Again I'm not an SQL specialist, but I wonder if the log file can be turned off? When adding so much data I supose the log file (and certanly on you USB disk) will dramaticaly slow down the process. I wonder if the log file can be temporary turned of until all data is in the DB. I supose this must be posible. Ofcourse you must no encounter any problem wile you do that, like a power failure. Erwin -----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 7: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