[dba-SQLServer] HELP - Log file growing rapidly

John W. Colby jwcolby at colbyconsulting.com
Mon Sep 6 20:29:56 CDT 2004


Michael,

May you have better luck than I.  Of course it really has nothing to do with
luck so much as experience.  I am not a SQL Server kinda guy so I am paying
the price of brute force and ignorance.

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 Michael
Maddison
Sent: Monday, September 06, 2004 8:51 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] HELP - Log file growing rapidly


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

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com







More information about the dba-SQLServer mailing list