August 4, 2010 at 2:36 am
Hi All,
I restricted the log file size of one of the database to 10GB.Every week the log file is growing up to 10GB and i am getting alerts.then i am shrinking the log file every time.I dont know why the log file is growing continuosly and hugely.I want to do permanent fix for it. can any one please let me know the permanent fix for this issue?
Thanks in advance,
Vamshi Madineni.
August 4, 2010 at 2:43 am
DB recovery model is FULL?
I Have Nine Lives You Have One Only
THINK!
August 4, 2010 at 2:45 am
Yes.DB Recovery model is Full.Even Auto Srink option is also enabled.the database is in Sql Server 2000.
August 4, 2010 at 2:47 am
You should create a maintenance plan to take log backup regularly.
If log backup is not taken, log entries remain in the file and file grows continuously.
Hope this helps you.
August 4, 2010 at 2:51 am
vamshi.sql (8/4/2010)
Yes.DB Recovery model is Full.Even Auto Srink option is also enabled.the database is in Sql Server 2000.
Disable Auto Shrink.
August 4, 2010 at 2:57 am
that is ok.i enabled auto shrink option,but why that is not happening once the log file is grown extremely..please let me know...
August 4, 2010 at 3:00 am
Yes.DB Recovery model is Full, Sql Server 2000
in this case you have to make backup db and after backup log wtih TRUNCATE_ONLY options
I Have Nine Lives You Have One Only
THINK!
August 4, 2010 at 3:07 am
vamshi.sql (8/4/2010)
that is ok.i enabled auto shrink option,but why that is not happening once the log file is grown extremely..please let me know...
Is log backup happening regularly?
AutoShrink does not remove the log entries from the file. Auto Shrink can shrink the file only if there is unused space in the file.
You should take log backup regularly to clear the log entries from the file and make room for new log entries.
August 4, 2010 at 3:15 am
handkot (8/4/2010)
Yes.DB Recovery model is Full, Sql Server 2000
in this case you have to make backup db and after backup log wtih TRUNCATE_ONLY options
I don't agree with this solution.
August 4, 2010 at 4:26 am
handkot (8/4/2010)
Yes.DB Recovery model is Full, Sql Server 2000
in this case you have to make backup db and after backup log wtih TRUNCATE_ONLY options
No, no, no! That is terrible advice to be tossing out without mention of the effects of truncate only.
Take a look through this article - http://qa.sqlservercentral.com/articles/64582/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 4, 2010 at 4:49 am
in this case you have to make backup db and after backup log wtih TRUNCATE_ONLY options
This is not advisable, Tuncating the log will not solve the issue.
A transaction log backup removed all the inactive transactions from the t-log file, which make spaces for the newer transaction. If you have a database with FULL recovery model, without any transaction log backup in place, you cannot perform a point-in-time restore in case of a disaster.
I suggest, if you don't want a point-in-time restore, then change the recovery model of the database to SIMPLE.
Thanks & Regards,
Sudeepta.
http://twitter.com/skganguly
August 4, 2010 at 5:10 am
As discussed earlier, please schedule a transaction log backup job. This is the permanent fix
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
August 4, 2010 at 8:59 am
The log doesn't grow like crazy for no reason. Activity in the database (data updates, additions, deletions) cause log entries. If you are in full recovery mode, this space cannot be reused until you take log backups. Once you do that, the committed transaction space is reused for new log entries. The more regularly you take the log backups, the less likely your log is to grow large.
However, that depends on activity in the database. 10GB might not be large enough. You shouldn't restrict the log from growing, however you should manage your space. Be aware of how much the log grows between backups, set the log a little larger than that, and you should be OK.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
August 4, 2010 at 10:14 am
Also reindexing will cause filegrowth, both on the data files and on the index files (I don't see bulk loading setting making a huge dent in this), and in many cases the tables being reindexed are too big for sorting in TempDB to be helpful. Make sure that you are taking log backups during or immediately after reindex operations or the log will fill drastically.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply