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

Jim Lawrence accessd at shaw.ca
Thu Oct 6 19:22:55 CDT 2005


Hi Ping:

That does seem very fast. You would not have mistaken the Event log file,
which also has size limits, for the transaction log file? Truncating -
shrinking the log file after a backup should be sufficient security. The
only time there would be an issue is if the server database ever becomes
corrupted as it would take longer to recover. 

3.5 million records is fairly average for a medium sized business.  

Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Ping Li
Sent: Thursday, October 06, 2005 12:18 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer] Help: What to do when transaction log file is
full

Hi, Jim and Francisco,

Thank you both for your suggestions.

I think maybe it's better to set up the data file for auto-growth by a 
small increment of MB.  But I don't quite understand the log file.  I 
mean, it has 3GB space allocated and the usage is only 4% when I checked 
with command: DBCC sqlperf(logspace).  Why did it give me log file full 
error when I tried to insert records?  Is 3.5million records really huge?

We don't need the transaction log so we set the recovery model to SIMPLE 
and backup the database with no log option.  That means we truncate the 
log file every day when we back up the database.  Should I shrink the 
log file first before I set the database to autogrow?  What would the 
file size be after it gets shrunk?  will it have performance hit later 
on when the file grows back large?  As I understand, truncate a log file 
doesn't free up the space for other use.  Only shrink can actually 
reduce the file size.

Thanks again.
Ping

dba-sqlserver-request at databaseadvisors.com wrote:

>Send dba-SQLServer mailing list submissions to
>	dba-sqlserver at databaseadvisors.com
>
>To subscribe or unsubscribe via the World Wide Web, visit
>	http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>or, via email, send a message with subject or body 'help' to
>	dba-sqlserver-request at databaseadvisors.com
>
>You can reach the person managing the list at
>	dba-sqlserver-owner at databaseadvisors.com
>
>When replying, please edit your Subject line so it is more specific
>than "Re: Contents of dba-SQLServer digest..."
>
>
>Today's Topics:
>
>   1. Re:  Help: What to do when transaction log file is	full
>      (Jim Lawrence)
>   2. Re:  Help: What to do when transaction log file is	full
>      (Francisco Tapia)
>
>
>----------------------------------------------------------------------
>
>Message: 1
>Date: Wed, 05 Oct 2005 11:20:50 -0700
>From: Jim Lawrence <accessd at shaw.ca>
>Subject: Re: [dba-SQLServer] Help: What to do when transaction log
>	file is	full
>To: dba-sqlserver at databaseadvisors.com
>Message-ID: <009601c5c9d9$8403e0a0$017ba8c0 at xpserver>
>Content-Type: text/plain; charset=us-ascii
>
>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
>
>
>
>------------------------------
>
>Message: 2
>Date: Wed, 5 Oct 2005 13:15:44 -0700
>From: Francisco Tapia <fhtapia at gmail.com>
>Subject: Re: [dba-SQLServer] Help: What to do when transaction log
>	file is	full
>To: dba-sqlserver at databaseadvisors.com
>Message-ID:
>	<b874372a0510051315g2deff45ev66a31c434346e52a at mail.gmail.com>
>Content-Type: text/plain; charset=ISO-8859-1
>
>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...
>
>
>------------------------------
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>
>
>End of dba-SQLServer Digest, Vol 32, Issue 4
>********************************************
>  
>

-- 
Ping Li
Developer/Analyst
CLA Fiscal Administration
208 Johnston Hall
101 Pleasant St SE
University of Minnesota
Minneapolis, MN 55455
(612)-626-0304
pli at umn.edu

_______________________________________________
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