January 2, 2008 at 12:04 am
What would be your first instinct when you see log file growing, and how do you usually handle it? does taking full back up of the database in full recovery mode automatically truncates the log.
Any suggestions will be highly appreciated.
January 2, 2008 at 12:59 am
Ziljan4 (1/2/2008)
What would be your first instinct when you see log file growing, and how do you usually handle it?
Check my tran log backups, make sure they are running. Check the replication, make sure the log reader hasn't failed.
Check the sys.databases view to see the reason the log in not been reused.
does taking full back up of the database in full recovery mode automatically truncates the log.
No. Only a log backup truncates the inactive portion of the log
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
January 2, 2008 at 1:02 am
taking full backup in full recovery model does NOT truncate transaction log. You have to take regular t-log backups.
Refer Below articles.
317375 INF: Transaction Log Grows Unexpectedly or Becomes Full on SQL Server
http://support.microsoft.com/?id=317375
272093 INF: Effects of Nonlogged and Minimally Logged Operations on Transaction
January 2, 2008 at 10:30 am
Good advice from the previous 2 posts.
From your post, it's seemingly growing faster than in the past - at this point, I would ask myself "why"?
A new process could have been implemented or an existing process changed which could be causing the additional growth and this could be fine - as long as you know what it is!
If something has changed and the transaction log patterns are changing, it may be worthwhile reviewing your log backups. Do they need to be more frequent or will they be fine as they are?
January 3, 2008 at 11:26 pm
Thank you so much for your time and effort. Like most of you said taking transaction log back up truncates the inactive portion of log and shrinks the t log file and hence solves the growing log problem. However if we truncate the T log ( back up log with truncate_only), won't this will invalidate the log sequence and may not allow us Point in time recovery. So, are there anyways where we can trucate the growing log file as well as keep log sequence valid.
Thanks
January 3, 2008 at 11:33 pm
Not if you back it up. Tha backup log only truncated what it has backed up, so your log chain is safe.
It's if you truncate without backing up that you break the log chain.
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
January 4, 2008 at 2:10 am
Gail answered your question but I would add a little advice on top of her post.
Your transaction log is growing for a reason. IE..You have many inserts, updates & deletes that are occuring on a regular basis. By shrinking the file daily, you are only incurring additional IO's when the log file is automatically grown again.
If I were in your position, I would leave the log at 3GB or if you have the space available, increase it to 4 or 5GB.
January 4, 2008 at 2:18 am
One thing I missedin your post.
A tran log backup doesn't shrink the log. It just truncates and discards the inactive portion of the log that it has backed up. The size on disk won't reduce after a log backup.
To reduce the size of the log file you need to explicitly shrink the log. As Clive said, not usually a good idea.
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
January 6, 2008 at 3:50 pm
Thank You again for clearing some misunderstandings.
So, can I conclude that the only way to resolve the growing log is to take T log back up which truncates the log?,
and also can we truncate T log without backing up. if so, could please tell me how it can be done?
Thanks
January 6, 2008 at 10:59 pm
Ziljan4 (1/6/2008)
Thank You again for clearing some misunderstandings.So, can I conclude that the only way to resolve the growing log is to take T log back up which truncates the log?,
Or switch the database to simple recovery mode, which means you won't be able to restore the db to point of failure.
and also can we truncate T log without backing up. if so, could please tell me how it can be done?
Switch to simple recovery mode, or Backup log with truncate_only.
Note that doing either breaks the log chain and means that you won't be able to restore the log backups past that point if the database need to be restored.
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
January 8, 2008 at 2:52 pm
First of all, realize that a log file is not "supposed" to be a certain size. As was mentioned earlier, the size is related to the activity in the database. A log file of a certain size is not necessarily a "problem" that needs to be "resolved" by shrinking it. 😀
1) Don't agonize over this unless you're about to run out of disk space.
2) Do Investigate. As was mentioned in a earlier post, there may be a new application, new process, more users, more transactions, etc. The log is growing for a reason. Find that reason.
3) Don't just truncate the log (again, unless you're about to run out of disk space). You'll just waste CPU cycles as the log file gets bulked up again, and if you do it wrong you break your backup chain.
4) Do verify your backup schedule. Regular T-Log backups can free up space inside the existing log file on disk and help reduce growth by allowing SQL Server to reuse that space instead of growing the log.
Steve G.
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply