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

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!