SQL Server Performance Forum – Threads Archive

Simple recovery mode db t-log not clearing

I have a database on my warehouse server. Each night its (large) tables are truncated, their indexes are dropped, then they are repopulated from another source. Following this, extensive copying/transformation is done on this data, into new tables in the same database, and also into another one on the same server. The db is in simple recovery mode. However, strangely, the t-log does not clear down completely after each night. Gradually residual space in it is consumed until it fills up. However if I execute CHECKPOINT in the db each morning it does clear immediately. So as a temporary workaround I’ve had to set up a scheduled task to execute checkpoint every morning. The strange thing is, this residual log usage is not all the log from one night’s load – that would be many GBs, but I am only talking about 200-300MB a night. So the majority of the log is being cleared, but not all. Anyone have any ideas? Tom Pullen
DBA, Oxfam GB

---

This model is similar to setting the trunc. log on chkpt. database option in version 7. As log truncation occurs at these points, at the completion of a BACKUP LOG statement or every time a checkpoint is processed, if the database is in truncate mode. Which I believe SQL Server is performing as per the behaviour of setup. Satya SKJ

---

Yes, but why is checkpoint not happening automatically? Checkpointing is not disabled for this database. For a database in 6.5 or 7.0 set to Truncate Log On Checkpoint, you do not have to manually issue checkpoints to get the log to truncate. So why I am having to with this database? In my opinion, SQL Server 2000 is NOT behaving "as per the behaviour of setup" in my case. Tom Pullen
DBA, Oxfam GB

---

I had no issues with SIMPLE recovery model and I believe if the database is in log truncate mode and the log becomes 70 percent full, CHECKPOINT will be fired to clear the log. I am not sure if PROFILER can help in this regard, may check this option too. HTH Satya SKJ

---

I was firmly in the belief that checkpoint happened automatically every second or so if not more often. It is required to keep flushing dirty pages in memory to disk, so must happen frequently. Does anyone else have any insights? Tom Pullen
DBA, Oxfam GB

---

I believe checkpoints are issued automatically, yes. But checkpoint regularity is dependant on the log size I think, so once a second may not always hold true if there is not much log activity.

---

Oh I see, it is all becoming clearer now. So because there is no update activity in this database after the load process completes, checkpoints will not be issued, so a portion of the log will remain un-truncated. It still seems strange to me that this can have a cumulative effect over several days, gradually filling the log. You would expect an automatic checkpoint to occur eventually, freeing up this log space. Seems like that doesn’t happen, though. I shall have to simply continue executing manual checkpoints every morning. Thanks for your help. Tom Pullen
DBA, Oxfam GB

---

_________
Satya SKJ

---

]]>