automatic checkpoint of transaction log

Question

automatic checkpoint of transaction log

Henry_Lee

SSCrazy

Points: 2714
More actions
November 17, 2009 at 7:35 am

#96479

Good day,
I have a database in SIMPLE recovery mode that doesn't seem to be checkpointing correctly. The transaction log just grows and grows and grows. And it's not that there's one really big transaction that's occurring. For instance, there has been no activity in the database for hours yet the log is sitting there at over 90% full. I know I could issue the CHECKPOINT command, but I'd like to understand why the automatic checkpoint is not occurring. What should I be looking for?
FROM BOL:
If the database is using the simple recovery model, an automatic checkpoint is generated whenever the number of log records reaches the lesser of these two values:
The log becomes 70 percent full.
The number of log records reaches the number SQL Server estimates it can process during the time specified in the recovery interval option.
The recovery interval for the server is set to 0.
Thank you.

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

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

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

Run DBCC OPENTRAN, post the results

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

Henry_Lee SSCrazy Points: 2714 More actions · Answer 2

Thanks Gail for the suggestion. Here are the results:

No active open transactions.

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

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

DBCC SQLPERF(LogSpace)?

Checkpoints don't run on a schedule, frequency depends on activity.

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

Henry_Lee SSCrazy Points: 2714 More actions · Answer 4

Thanks again...

DBCC SQLPERF(LogSpace) shows that the log is 95% used.

I understand it doesn't run on a schedule, but I would think the next transaction that comes along, it would see that the log is 95% used and issue a CHECKPOINT. Instead, the log just grows and will soon cause the auto grow to kick in...

Henry_Lee SSCrazy Points: 2714 More actions · Answer 5

This is an old database and the log was set to auto grow by small increments so I thought maybe over time there became too many virtual log files. So I shrunk the file all the way down to 1mb then immediately sized it to it's correct size of 1gb, but that didn't seem to help.

Right now the log file is at 96% full. If it auto grows before checkpointing, then my next thought is to create a brand new log file, empty the old one remove it and see what happens.

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

Odd. You're absolutely sure this database is in simple recovery?

If you run a manual checkpoint does the log usage drop?

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

Henry_Lee SSCrazy Points: 2714 More actions · Answer 7

Fair question...I had to double check myself, but yes, it's in SIMPLE.

Yes, manually executing CHECKPOINT flushes the log.

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

Very strange. Not sure what's happening there.

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

Henry_Lee SSCrazy Points: 2714 More actions · Answer 9

The last couple hours I've been watching the log go from 95, to 96...it's now over 99% full. I expect auto grow to occur at any moment. If that occurs, as mentioned, my next attempt will be to create a new log file and remove the old one and see what that does. I'll post the outcome here for posterity.

Thanks for your help.

Joie Andrew One Orange Chip Points: 27360 More actions · Answer 10

Does the log contain anything saying it even tried to do a checkpoint?

Joie Andrew
"Since 1982"

Steve Jones - SSC Editor SSC Guru Points: 735611 More actions · Answer 11

Couple other questions. When activity is occurring in this database, is it one large transaction that might be occurring? Or implicit transactions set on?

How many log files do you have?

Definitely seems strange here.

Henry_Lee SSCrazy Points: 2714 More actions · Answer 12

Thanks for the response guys..

Unixbomer: Yes, "SELECT * FROM ::fn_dblog(NULL, NULL)" does show a checkpoint occurred, however it was over 12 hours ago.

Steve: It's tough to tell the size of the transaction, however, I do not believe they are big. I've been watching the log space used all day using "DBCC SQLPERF(LOGSPACE)" and it fills slowly and in small increments. If they were big transactions I would expect to see the log fill up faster or in larger increments. Also, the server setting IMPLICIT_TRANSACTIONS is OFF.

So this morning I learned about "DBCC LOGINFO" - and how that displays the number of VLF's. This database had hundreds of them, a result of small growth increments occurring over time. (Earlier, I indicated I shrunk the log file and immediately resized it, which would have corrected this, however, either I meant to and didn't or it didn't work, or I did it on another database, not sure. Sorry if that threw folks off.) However, this morning I definitely did and I confirmed the the number of VLF's dropped from the hundreds to maybe a dozen.

I'm not sure if this will solve it but I'm keeping my eye on it. I presume I won't know until I get up around 70% log space used and see if the automatic checkpoint kicks in. I'm only at 3% at the moment.

Also noticed the databases I'm having issues with, "DBCC LOGINFO" shows most of the VLF's with a status of "2" meaning they are part of the active log. I'm not sure why that would matter, I don't know why that would prevent a checkpoint, if anything those are the records that actually should be checkpointed and flushed to disk.

I feel like I have all the pieces to this puzzle right under my nose, I just haven't put them together yet. If I can come up with a coherent explanation I will post it.

Paul Randal One Orange Chip Points: 29544 More actions · Answer 13

My guess is you've hit a known bug in 2000 SP4.

See my old blog post Why won't my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs for more details.

And let us know if this solves your problem.

Cheers

Paul Randal
CEO, SQLskills.com: Check out SQLskills online training!
Blog:www.SQLskills.com/blogs/paul Twitter: @PaulRandal
SQL MVP, Microsoft RD, Contributing Editor of TechNet Magazine
Author of DBCC CHECKDB/repair (and other Storage Engine) code of SQL Server 2005

Henry_Lee SSCrazy Points: 2714 More actions · Answer 14

Thanks for the suggestion Paul.

So I had to wait until after Thanksgiving for my maintenance window. I downloaded the Hotfix and went to install it and got this message:

This SQL Server instance you are currently running already contains the update you are trying to install. You do not need to install the update. A Hotfix of build equal to or greater than the following should be downloaded: 2280

Alas, enough time spent on this when the short term solution is to schedule a job to do the CHECKPOINT periodically and the long term solution is to upgrade the machine to 2008.

Thanks all for your suggestions.