October 11, 2009 at 10:52 pm
Hi,
i have a databse on SQL 2000. The recovery model of the database is set to Simple. But the transaction logs are not getting truncated and we are getting issues with transaction log file full.
Whenever I run a check point command explicitly the log file gets truncated and the percentage utilization becomes around 3 to 4 %.
As per my understannding the autocheckpoint should happen when the log file becomes 70% full. Is there anything that I'm missing? Please help me in resolving the issue?
October 11, 2009 at 11:06 pm
is recovery interval set to 0? What sql 200 version are you running? Is there any active transactions running when you do not see the checkpoint occur?
October 11, 2009 at 11:14 pm
yes, recovery interval is set to 0.
I'm using SQL 2000 SP4 and yes there are active transactions. But there are active transactions when I run checkpoint explicitly as well.
October 11, 2009 at 11:24 pm
transactions can be held for many reasons. My guess is when you run the checkpoint the transaction causing the issue has since completed. Is this database replicated?
Is it you do not have space on the disk? Is the log file set to auto grow?
October 12, 2009 at 3:32 am
I think you need to investigate why you have long running transactions.
"Keep Trying"
October 12, 2009 at 3:37 am
I would also check to see if you have any long running jobs, or anything that has changed recently.
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 12, 2009 at 6:20 am
Beginer (10/11/2009)
As per my understannding the autocheckpoint should happen when the log file becomes 70% full.
No, that's for TempDB. For user databases it runs regularly to keep the expected recovery time below the required. It's usually every couple minutes.
Do you have replication set up? At the point that he log is very full, what does DBCC OPENTRAN return?
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply