October 11, 2010 at 5:43 am
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 .
October 11, 2010 at 5:50 am
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.
October 11, 2010 at 5:53 am
are you doing t-log backups?
October 11, 2010 at 6:08 am
NO we are not doing T log backup.
October 11, 2010 at 6:09 am
Thanks for the reply.will be there any performance impact while running sql profiler?Will trc file occupy more space.
October 11, 2010 at 6:14 am
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.
October 11, 2010 at 6:19 am
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
October 11, 2010 at 6:25 am
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...
October 11, 2010 at 6:28 am
No to solve the problem you need to schedule regular t-log backups.
October 11, 2010 at 6:37 am
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)
---------------------------------------------------------------------
October 11, 2010 at 7:09 am
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
October 11, 2010 at 7:35 am
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://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
October 11, 2010 at 7:52 am
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.
October 11, 2010 at 8:14 am
If we are taking transaction log backup in Principal database(database mirroring),will it effect the transactions happening to mirror database in any way.
October 11, 2010 at 8:25 am
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.
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply