sqlskills-logo-2015-white.png

Why won’t my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs

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.

4 thoughts on “Why won’t my log clear in SIMPLE recovery mode? SQL 2000 bug or very large VLFs”

  1. Hi Paul,

    Thanks for posting this! I am facing this problem while purging the data from few VLDBs which are in Simple revovery. I guess this hotfix will take care of that.

    -Amit

    Reply

  2. I ran in to the SQL 2000 checkpoint bug several times at a previous job. I don’t know if this is just a coincidence, but it seemed to occur much more frequently in tempdb than in other databases.

    -Larry Taylor

    Reply

  3. Hi Paul

    Wondering if you have an updated version of this?

    I have found a couple of others that may be of interest:

    1. Backups in progress seem to prevent checkpoint in Simple recovery
    2. Disk IO latencies seem to be able to trigger a throttle in checkpoint that can end up slowing it down massively.

    Cheers

    Rich

    Reply

    1. 1) The log will never clear while a data backup is in progress, regardless of recovery model.
      2) Correct. I discuss this in my Pluralsight course on logging, and it’s also discussed in the SQL Server I/O internals whitepapers.

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *

Other articles

Imagine feeling confident enough to handle whatever your database throws at you.

With training and consulting from SQLskills, you’ll be able to solve big problems, elevate your team’s capacity, and take control of your data career.

Collaborate With Us
Choose a Course