log file growing tremendously

Question

log file growing tremendously

shanila_minnu

Hall of Fame

Points: 3465
More actions
October 11, 2010 at 5:43 am

#229471

I have set up database mirroring with 2 GB data.But its log file is growing up to 20 Gb within 3 days.
Could you please suggest what is the root cause and also please give a permanent solution for this apart from shrinking the log file .

Viewing 15 posts - 1 through 15 (of 29 total)

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

eshueshwar Old Hand Points: 380 More actions · Answer 1

You could run the sql profiler trace to capture the log file details, which will let you know while carrying which activities the log file grows. Once we know the root cause then its easy to work towards the solution.

steveb. SSC-Forever Points: 46733 More actions · Answer 2

steveb.

SSC-Forever

Points: 46733

October 11, 2010 at 5:53 am

#1234919

are you doing t-log backups?

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 3

shanila_minnu

Hall of Fame

Points: 3465

October 11, 2010 at 6:08 am

#1234924

NO we are not doing T log backup.

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 4

Thanks for the reply.will be there any performance impact while running sql profiler?Will trc file occupy more space.

Dwayne Dibley SSCrazy Points: 2350 More actions · Answer 5

shanila_minnu (10/11/2010)
NO we are not doing T log backup.

...and there lies your problem. I would suggest that you take a read of backup & restore concepts to fix your problems. The background information that it provides will help fix your problem and explain why it is happening.

steveb. SSC-Forever Points: 46733 More actions · Answer 6

shanila_minnu (10/11/2010)
Thanks for the reply.will be there any performance impact while running sql profiler?Will trc file occupy more space.

Don't bother running a SQL profiler trace, it won't help you solve the issue.

What you need to do is setup t-log backups at regular intervals

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 7

rjohal-500813 (10/11/2010)
shanila_minnu (10/11/2010)
NO we are not doing T log backup.
...and there lies your problem. I would suggest that you take a read of backup & restore concepts to fix your problems. The background information that it provides will help fix your problem and explain why it is happening.

Thank you very much....

How can we fix the problem by just restoring a backup...

steveb. SSC-Forever Points: 46733 More actions · Answer 8

No to solve the problem you need to schedule regular t-log backups.

george sibbald SSC Guru Points: 104210 More actions · Answer 9

do you have 20Gb of space to do a T-log backup because it is likely to be that size.

what is the result of running dbcc sqlperf(logspace)

---------------------------------------------------------------------

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 10

george sibbald (10/11/2010)
do you have 20Gb of space to do a T-log backup because it is likely to be that size.
what is the result of running dbcc sqlperf(logspace)

dbcc sqlperf(logspace) is giving 1.5% usage of 20 GB log file.I want to submit the root cause of this growing TLOG.Any sugessions

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

shanila_minnu (10/11/2010)
I want to submit the root cause of this growing TLOG.Any sugessions

As has been said more than once on this thread, the root cause of the growing transaction log is that you are not running transaction log backups.

In full recovery, you must run transaction log backups, or the log will grow until it fills the drive.

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

homebrew01 SSC Guru Points: 55548 More actions · Answer 12

General Guidelines - (specifc "best" options vary widely based on business needs) :

Create a maintenance Plan to run Transaction Log backups every 15 minutes or so.

Create a cleanup step that deletes the t-log backups over 'x' days or weeks old.

I suppose I should ask if you are running FULL database backups ?

If not, immediately backup your database, then create a maint plan to run nightly for FULL backup.

Create a cleanup step that deletes full backups over 'x' days or weeks old.

Put your FULL & T-Log backup files to tape each day.

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 13

If we are taking transaction log backup in Principal database(database mirroring),will it effect the transactions happening to mirror database in any way.

shanila_minnu Hall of Fame Points: 3465 More actions · Answer 14

General Guidelines - (specifc "best" options vary widely based on business needs) :

Create a maintenance Plan to run Transaction Log backups every 15 minutes or so.

Create a cleanup step that deletes the t-log backups over 'x' days or weeks old.

homebrew01 (10/11/2010)
I suppose I should ask if you are running FULL database backups ?
If not, immediately backup your database, then create a maint plan to run nightly for FULL backup.
.

Actually we are taking DPM backup .So backups are not scheduled at all.