Quick post this morning before this gets knocked out of my head by the Disaster Recovery session we're doing today at SQL Connections. This came up yesterday for a few people both on 2000 and 2005 – the database is running in SIMPLE recovery mode but the log isn't getting cleared as it usually does with checkpoints. There are two possibilities here – one for 2000 and one for both 2000 and 2005.

Very large VLFs

If the transaction log is comprised of very large (multi-GB) virtual log files, then a checkpoint operation may not clear out all the active log from the VLF – and so the log may not clear as frequently as when its comprised of more, smaller VLFs. Kimberly's blog post yesterday touched on this plus a solution.

SQL 2000 checkpoint bug

This is one I hadn't heard of. There's a bug in 2000 SP4 that stops checkpoint working properly on some configurations and so the log file can grow until it runs out of space. A few people in yesterdays Database Maintenance class were hitting this. Searching through the Knowledge Base articles turned up KB 909369 which explains the issue and has a pointer to the hotfix.

Hope this helps some of you out.