Mark L. Breen
subs1847 at solution-providers.ie
Sat Aug 28 11:33:22 CDT 2004
Hello John, I have just downloaded my email for the list and there was in total 5500 or so emails, so I have just read through your series of problems with SQL. Firstly, I envy you, you lucky guy, having such a big db to work with ! Secondly, sorry to hear about the amount of hassle that you have had. I have recently been working on a 2gb database and when it is on the server, my laptop runs fine, but on my laptop with 1/2 gb ram, the laptop grinds to a halt, so I would be in favour of running the db off the development machine. I know that you have already looked at some of the obvious things such as ensuring that there are no indexes, in the tables, ensuring that the fields are no bigger than they need to be, use integers where you can etc. I have a few other comments to make, although I do not think that there are any revelations in them. DTS is probably the best way to get data in. SQL 2000 better at truncating / shrinking than SQL 7. Have you considered just importing a few hundred records from each file and getting the thing up and running? I am presuming that once you get it imported you want to start development. Wouldn't it be nicer to work with just 1 million records for the initial development period. Have you considered building a little vb app that would import 10k records and then do a database integrity and index rebuild (the work that the maintenance wizard does). Then let the vb app just work away for a week or so, mean while you could continue to use the hardware that you have. For the duration of this project, would you consider forgetting about redundancy and just use what ever disk space you have? I also have a dislike (not based on fact, just emotion) for compression. I wonder how it will effect performance of the SQL Server if you ever get it fully loaded. Keep in touch and let us know how the project goes Best of luck, Mark ----- Original Message ----- From: "John W. Colby" <jwcolby at colbyconsulting.com> To: "'Access Developers discussion and problem solving'" <accessd at databaseadvisors.com>; <dba-sqlserver at databaseadvisors.com> Sent: Saturday, August 28, 2004 12:56 PM Subject: [dba-SQLServer] VLDBs, the saga - Ramblings only > I have been working on getting a rather large database into SQL Server. > This thing is ~65 million names plus demographics info and will be used for > bulk mailing analysis. I have been struggling for weeks to make this > happen. Having no experience with a database this big, I had no idea how > big the final db would be. I tried to get it in to a 160g drive but it > rapidly became obvious that wouldn't hold it. I then purchased two 200g > drives and used Raid0 to make a big 400 g drive. I thought I turned on > compression but after getting well into the extraction process I discovered > this wasn't the case. I then started trying to figure out how to get the > drive compressed. > > Long story short, a NTFS drive can be compressed, even a raid array such as > this, however... There is a control that allows you to select the sector > size. I had selected the "compress" check box but then selected a sector > size of 64K. As I started investigating why the drive wasn't compressed it > turns out that only sector sizes of 512 to 4K bytes allow compression. > Anything larger causes the "compress drive" check box to gray out and the > drive ends up uncompressed. > > By this time I had already spent several days extracting zipped files of > data and BCPing them into SQL Server so I had a MDF file of over 300gb and > no place to put it! > > Sigh. > > Out of desperation I decided to try zipping the database file. I started it > PK zipping last night onto an empty 160g partition. This morning I had a > 10gb zipped file that supposedly contains the MDF file! > > I then deleted the partition on the 400gb Raid array and started playing > with the compression / block size which is when I discovered the >4K sector > size gotcha. I set the sector size to 4K and quick formatted, then started > unzipping the MDF file to the (compressed) 400gb raid array. > > We shall see. The unzip is not finished, in fact has several hours to go > yet. If this works I will celebrate. > > This whole project has been a challenge. It looks like the database will be > around 600g for JUST the data, never mind any indexes. I simply don't have > the money to build a raid 5 array to up the uncompressed drive size. Even > if I did, IDE drives larger than 250gb are expensive and AFAICT only > available in 5200 RPM. Plus the overhead of Raid5 is "One Drive" which > means I'd need (4) 300g drives to build a 900g usable space raid5 array. > Raid1 (which I am currently using) tops out at 600g using (2) 300g drives > (uncompressed). So far my (2) drive Raid1 array using 200g drives has cost > me $240 plus a controller I already had. A Raid5 solution using 300g drives > would cost about $1200 just for the new controller and 4 drives! > > With any luck, given the massive compression PKZip managed to attain, I will > be able to shoehorn the 600g. > > Update 8-( > > As I write this I just got a "delayed write failed" message from Windows > saying it lost data trying to write to the disk. I have tried to turn off > write caching but can't seem to find the magic button to cause Windows to > quit using "Delayed write". > > BIG sigh! > > If I can't get the db back out of the zip file I will be facing a weekend of > "starting from scratch" on getting the data out of the raw text files and > back in to SQL Server! > > And I thought this would be fairly easy. > > 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 > >