[dba-SQLServer] Help: What to do when transaction log file is full

Ping,
You acutally do not need a log file that is that huge in size, at least not
typically. Some DBAs will argue with unresctricted growth on the data file,
but imnsho, it's not a huge issue to allow the data file to grow by at least
10%. The log file does not have this need. If you shrink the log file to
something like 50mb, you can monitor it's usage, and have it to auto-backup
when the transaction lone reaches 60% of capacity by setting up an alert and
having the alert performe the backup job when the log file is at 60%. If you
monitor the growth of the transaction log you'll find that it may not be
growing that fast, and thus you can possibly shrink the log file even
further possibly 25mb or 15... depends really on how heavy your server is
being used.

In books online lookup Sql Agent Alerts to find out more on what else you
can do w/ them.

hope this helps.

On 10/5/05, Jim Lawrence <accessd at shaw.ca> wrote:
>
> Hi Ping:
>
> My recommendation would be to let the database grow automatically and
> monitor how much space is being used. If you backup regularly and shrink
> only when necessary there should be no problem. If you set a fixed limit
> and
> the database is live there can be some serious ramifications if it
> suddenly
> runs out of space.
>
> My two cents worth.
> Jim
>
> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ping Li
> Sent: Wednesday, October 05, 2005 7:19 AM
> To: dba-sqlserver at databaseadvisors.com
> Subject: [dba-SQLServer] Help: What to do when transaction log file is
> full
>
> Hi,
>
> I am absolutely new to DBA tasks and I really need help from you all.
>
> Last week, after I used DTS wizzard to copy a huge table as a backup
> copy, I used query analyzer to truncate the old table and insert new
> records into it. It ran for a long time and came back with an error
> message saying "the log file is full, free up some space...". I checked
> the database. It was set to use simple recovery model and with a static
> size. The database is set to backup complete with no log option. The
> reason for what is that the database is more of a data mart than
> transactional database.
>
> The settings are as follows:
> Data file: space allocated: 100000MB.
> Log file: space allocated: 3000MB; automatically growth--checked; file
> growth: 1000 in MB; Maximum size: restricted file growth(MB) 3100
>
> I talked with somebody. Some suggested me change the database to grow
> automatically or auto-increment by 10% while the other strongly urge not
> to set it to grow automatically. One of people I talked shrink the log
> regularly.
>
> I would appreciate any imput on what's the best practice in managing log
> files. Thank you,
>
> Ping Li
> _______________________________________________
> 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
>
>


--
-Francisco
http://pcthis.blogspot.com |PC news with out the jargon!
http://sqlthis.blogspot.com | Tsql and More...