Log File is growing continuosly

Question

Post reply

Log File is growing continuosly

vamshi.sql

SSCommitted

Points: 1765
More actions
August 4, 2010 at 2:36 am

#387497

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.

Viewing 14 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

handkot SSCertifiable Points: 5250 More actions · Answer 1

DB recovery model is FULL?

I Have Nine Lives You Have One Only
THINK!

vamshi.sql SSCommitted Points: 1765 More actions · Answer 2

Yes.DB Recovery model is Full.Even Auto Srink option is also enabled.the database is in Sql Server 2000.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 3

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.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 4

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.

vamshi.sql SSCommitted Points: 1765 More actions · Answer 5

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...

handkot SSCertifiable Points: 5250 More actions · Answer 6

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!

Suresh B. SSC-Insane Points: 22986 More actions · Answer 7

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.

Suresh B. SSC-Insane Points: 22986 More actions · Answer 8

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.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

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://www.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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Sudeepta SSCommitted Points: 1666 More actions · Answer 10

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

Adiga One Orange Chip Points: 27284 More actions · Answer 11

As discussed earlier, please schedule a transaction log backup job. This is the permanent fix

Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 12

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.

jeff.mason SSCrazy Eights Points: 9585 More actions · Answer 13

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.