| Author |
Topic |
|
drsloat
Starting Member
45 Posts |
 Posted - 2007-08-08 : 15:23:24
|
| If my database is in simple recovery mode and has truncate log on checkpoint option set, then how can my transaction log be filling up to 100% used and growing? The log doesn't seem to be truncated until I do a backup log .. with truncate_onlyBOL: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. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-08 : 15:29:33
|
| You are probably running very large transactions. It won't clear the log until the transaction completes. This is in case it needs to rollback the transaction.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
 |
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-08-08 : 16:05:12
|
| I'm not doing large transactions, but there are many transactions going on. I've run DBCC OPENTRAN and I frequently get no open transactions.Can i monitor when checkpoints occurr? I looked in the perfmon counters, but didn't see anything that looked like this. |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-08 : 16:44:40
|
| You can run checkpoint manually to see if has any impact on transaction log. |
|
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-08-08 : 17:37:54
|
| I may try a manual checkpoint to see what happens, but i'm a little nervous to experiment with this live system.In my actual scenario i had transactional replication going. I understand that if there are transactions that have not been replicated the log would not be truncated, but the latency was not excessively high, so I assume that eventually the log would have gotten to the point where it could truncate. Instead the log just kept growing and growing (Each growth was 1024 MB and casued disruptions to my app).If the log can't be truncated at the time a checkpoint is created will it wait until the next checkpoint? When I deleted the publication it was truncated.Today I've been monitoring the log the whole day without replication. The percent used has slowly grown up to 40. I would expect that after almost a full day on a busy system we should have reached some auto checkpoints caused by the recovery interval option and that would have taken the % used back down near 0? |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-08-08 : 21:20:05
|
| Don't think will go to 0 in active db. The other option is backup log in schedule. |
|
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-08-09 : 00:49:27
|
| I turned my transactional replication back on and let my 15 GB logfile get up to 25% used. I then issued a manual CHECKPOINT command. It took about 2 minutes to finish executing at which time my log was now down to .4% used.That all makes sense to me, but it just doesn't seem like the auto checkpoints are getting fired. I do a log backup once a day, so maybe i'll increase that frequency or just let the log file grow a lot bigger. I'm just hoping to fully understand the situation. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2007-08-09 : 00:57:49
|
quote:
I do a log backup once a day
I thought you were using SIMPLE recovery model.We backup our transaction logs every 15 minutes. We use transactional replication also. If we backed up our tlogs say every hour, our tlogs would be huge due to replication.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/ |
|
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-08-09 : 09:42:42
|
| Thanks for your message. It is helpful to know how other people configure their systems.I am using SIMPLE recovery. The whole point of this question is why the log isn't truncated because of the "truncate on checkpoint" and the auto checkpoints mentioned in the first post. The question is academic, since i can work around it. |
|
|
|
drsloat
Starting Member
45 Posts |
Posted - 2007-08-09 : 15:44:02
|
| This describes the problem exactly, and our build is prior to the fix. I'm betting this is the cause of the behaviour.http://support.microsoft.com/kb/909369/FIX: Automatic checkpoints on some SQL Server 2000 databases do not run as expected |
|
|
|
|
Transaction Log Size in Simple Recovery