Michael Maddison
michael at ddisolutions.com.au
Mon Sep 6 19:51:12 CDT 2004
My fav sql site... http://www.sql-server-performance.com/default.asp Maybe you'll find something here to help. I've never been 'lucky' enough to work with a db that size yet! I'm waiting for a client send send me a DVD with about 10mill rows on it so I've been following with interest ;-) cheers Michael M -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Jim Lawrence (AccessD) Sent: Tuesday, 7 September 2004 8:12 AM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] HELP - Log file growing rapidly Hi John: Here is a site with information specifically on SQL 2000 that you may not have already seen. There is some very useful information on bulk importing of data: http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en- us/p art2/c0661.mspx (watch for wrap) There is a great section on warehousing and using the OLAP tools. I hate to be a pessimist but it seems even if you had an over-clock Cray mainframe, suspended in a bath of liquid nitrogen you could not have all the data translated by this week so you may have to seriously think of managing your data in chunks so you will have something to demonstrate to your client. HTH Jim -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Monday, September 06, 2004 1:50 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] HELP - Log file growing rapidly Eric, Thanks for that detailed method of doing this stuff. I am working to figure out the backup piece as we speak. I had figured I would simply get all the data into the db then backup as soon as I finished, but I guess that simply isn't possible. I have a huge disk just for the log file, I don't much care if the log file grows huge as long as it works. When I first started this (two weeks ago) I was doing manual DTS operations to get each file into the db in sequence. I actually got about 11 of the files in (of 22 total files) but ran out of disk space - I was using (2) 200g drives Raid 0 for a 400g workspace and a single database file. The db file was 300g after 11 files so I could see that the rest of the data wasn't going to fit. I stopped, ordered a bunch of hardware, set up a dedicated machine, learned about multiple file databases, and set up 4 files plus a log file on 5 different 250g disks. It appears that WILL be enough space, probably even without backing up the log file. The log file 2 weeks ago (after 11 files) was only 11gb for a 300g data file so there is more to this than meets the eye. I now have a 250g drive dedicated to the log file for (4) data files which will (in the end) total by my estimations about 600 gb. The last few days have been agony simply because I was trying to speed things up and didn't know enough to do so. Problem after problem has caused me to waste the last two days. I have decided to just go back to my original method, and use DTS to import each file, one at a time, in sequence. That was working 2 weeks ago, and I PRAY it will work now. It will take me a full day to do it that way but then I have wasted TWO full days trying to do it a faster way. Ain't life grand? 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 Eric Barro Sent: Monday, September 06, 2004 4:24 PM To: dba-sqlserver at databaseadvisors.com Subject: RE: [dba-SQLServer] HELP - Log file growing rapidly John, Here's what I had posted earlier to deal with the growing log file issues... Overview: You will need to restrict the growth of the transaction log file and then set up a backup schedule for the log file followed by a shrink log file operation. 1. Right click on the database and then go into Properties. 2. Click the Transaction Log tab 3. Make sure to restrict file growth to a manageable size where your drive won't run out disk space 4. Click Options tab 5. Check the Auto shrink checkbox. This will allow SQL server to automatically shrink your database and log files to the minumum required. 6. Click OK and apply all those changes. 7. Go into the Management section of EM. 8. Select Backup 9. Create a backup set for your log files. Select the db, check Transaction Log radio button. Select destination and check the schedule box and specify when you want it to run. Depending on the amount of activity and the maximum amount you specified for the log file to grow you will need to specify a scheduled backup and shrink operation that will suit your needs. On our production environment I had it set to 1 hr intervals. 10. Set up a scheduled shrink log file operation. --- Eric Barro Senior Systems Analyst Advanced Field Services (208) 772-7060 http://www.afsweb.com -----Original Message----- From: dba-sqlserver-bounces at databaseadvisors.com [mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of John W. Colby Sent: Monday, September 06, 2004 10:44 AM To: dba-sqlserver at databaseadvisors.com Subject: [dba-SQLServer] HELP - Log file growing rapidly I am importing this data into the server. The log file is growing rapidly. It is currently at 23g, where the 4 data files are around 13g each. Is this natural or is it growing this rapidly because I am importing simultaneously from the 4 different machines? With 144g of free space on the drive that the log file is on, I am in no danger of running out of room, certainly not before the current set of imports finish. However it appears that I do need to figure out how to backup the log files so that I can re-use the room for the next set of imports. 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 _______________________________________________ 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