Infrastructure at your Service

Nathan Courtine

SQL Server Database failed to generate a checkpoint

Last time, I experienced a strange error when I tried to perform an integrity check or a simple checkpoint of a database:

One or more recovery units belonging to database ‘mydatabase’ failed to generate a checkpoint. This is typically caused by lack of system resources such as disk or memory, or in some cases due to database corruption. Examine previous entries in the error log for more detailed information on this failure.

The log scan number (26001:414:0) passed to log scan in database ‘ mydatabase ‘ is not valid. This error may indicate data corruption or that the log file (.ldf) does not match the data file (.mdf). If this error occurred during replication, re-create the publication. Otherwise, restore from backup if the problem results in a failure during startup.

As I said, this error seemed strange to me because:

  • Memory was available for my instance
  • The volumes space was enough
  • Tempdb data and log files were not completely used (as for ‘mydatabase’ files)
  • No database corruption was detected so far, and no dump
  • No more information are present in the error logs

I had no idea about the origin of this problem. So I decided to check the configuration of my database:

SELECT * FROM sys.databases where name = N‘mydatabase’

And when I checked the different configurations, I noticed that the parameter ‘log_reuse_wait_desc’ was set to ‘Replication’.

This database contains a replication which was not correctly cleaned. After some investigations, I decided to use the stored procedure called ‘sp_removedbreplication’:

EXEC sp_removedbreplication N‘mydatabase’

However, when I still found my database when I executed this query:

SELECT name, log_reuse_wait_desc FROM sys.databases where log_reuse_wait_desc = N’replication’

But the problem was the log file still contained a non-replicated transaction. My colleague advised me to mark all the transactions as replicated:

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

Unfortunately, the replication was still not cleaned and the error was still present! So, the only way was to reactivate the publication…

Here are the different steps I had to perform to resolve my problem:

  • Installation of the Replication components for this instance
    • Launch SQL Server installation, and add ‘Replication’ feature to the instance
  • Creation of a Publisher
    • As a table with a primary key is required for the replication, you should create an empty table just for this resolution (you can remove it at the end)
    • Add the database to the Publisher

Replication_Publiation

Now, the parameter ‘log_reuse_log_wait_desc’ must have changed from ‘Replication’ to ‘Nothing’. You can check it with this command:

SELECT name, log_reuse_wait_desc FROM sys.databases where name = N’mydatabase’

Re-execute the stored procedure to correctly clean the replication from the database:

EXEC sp_removedbreplication N‘mydatabase’

And your problem must be resolved 😉

Do not forget to remove the elements added with your replication (jobs, table …)

I hope it will help you. If you have questions or remarks, do not hesitate to comment!

6 Comments

  • ezek says:

    I had the Same problem and used exact same, procedure and i was helped, big time. Thanks Nathan.

  • Jim Tall says:

    Saved my ass! Can’t thank you enough for posting this.
    Now, if I only knew what caused this to begin with…

  • Nathan Courtine says:

    Glad to hear it 😉
    Regarding the root cause, was the database previously part of a replication? This is a typical scenario.

  • Matt says:

    Having the same issue, but the DB was never part of a replication schedule. It started occurring after a Veeam migration from one hyper-visor host to another.

  • Thomas says:

    Question to:
    “As a table with a primary key is required for the replication, you should create an empty table just for this resolution (you can remove it at the end)”

    Where should I add an Empty table – In the Database I’m having trouble with?
    (there are a lot of questions creating a new publication, and my DB is ~700GB, so if a complete publication WITH data is needed, I’m running out of space.

    “Add the database to the Publisher”

  • Nathan Courtine says:

    To fix the issue, the parameter ‘log_reuse_log_wait_desc’ needs to be changed from ‘Replication’ to ‘Nothing’.
    In this resolution, the database needs to be part of a replication as a Publisher. To do so, a table with a primary key (I chose an empty one) must be added at the initialization.
    Once the error is fixed, the database can safely be removed from the replication.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Nathan Courtine
Nathan Courtine

Senior Consultant