The transaction log for database is full due to ‘REPLICATION’. “Replication not enabled.” CDC

Welcome to Monday Morning Madness. What do you do when your Transaction Log runs out of space? Let me share my most recent experience.

My transaction log had a hard limit of 2TB in size. There are different opinions out there among the DBA elite on whether you should have a limit on the growth of your transaction log or if you should leave it as unlimited. As a BI Developer I do not have an opinion here, just note that I am happy that in this instance I had a hard limit.

The Error Message:

The transaction log for database <Database Name> is full due to ‘REPLICATION’.

Oh that’s it?? Easy fix right. Let’s walk through troubleshooting this problem.

  • My database is in SIMPLE recover mode, so why is it waiting to checkpoint?
  • I run the following command to find out the reason the log is waiting to clear.
USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = '<Database Name>';

image

Replication, really? Who turned on REPLICATION!!?? I had no idea replication was turned on. Let’s find out.The following code will show if replication is turned on for any of my databases:

SELECT name, is_published, is_subscribed, is_merge_published, is_distributor
FROM sys.databases
WHERE    is_published = 1 or is_subscribed = 1 or
        is_merge_published = 1 or is_distributor = 1
      Ok so here is my result set from the above query:

image

  • So to recap, the transaction log is not purging itself because of a long open transaction due to replication but replication is not turned on?? DBCC OPENTRAN will show open transactions.

Well after some further research I discovered that my Change Data Capture job had failed. More specifically the CDC_Capture job. We turned on CDC to capture some otherwise hard to capture changes. The way CDC works is it crawls the transaction log and once it has completed with a section of the log that part of the log can then be cleared and reused. In my case CDC job failed and as a result none of the transaction log was being allowed to clear.

Note: By default when you enable CDC it will create two jobs. One that captures the changes and the other that cleans up the tables where the changes are stored. The schedule to run these jobs is configured to ONLY start upon starting of SQL Agent. I would recommend modifying the scheduling of this job.

So finally we discovered the problem. Now how do we fix this?

  1. First try turning on the CDC Capture job. (This doesn’t work because the transaction log is full.)
  2. Try shrinking the database. (Well this won’t work for two reasons. One transaction log is full, secondly because there is no free space to shrink.)
  3. Third you can try changing the max limit on your transaction log. Bet you can’t guess what happens when you do this? This doesn’t work because the transaction log is full.

None of the above situations will work here because they all require at least some space on the transaction log to complete.

  1. Ultimately I ended up creating a new transaction log on my database.
    1. This is why I was glad my original transaction log had a “Hard” limit. If there was no hard limit then I ultimately would have completely run out of space on my disk and I would not have been able to simply create a second transaction log.
  2. Once the new transaction log was in place I then disabled CDC on the database. I disabled it because the client was no longer using it. Alternatively I could have turned on the job and just let it crawl the entire transaction log, all two TB Smile.
  3. Once CDC was disabled I ran the following checkpoint command to checkpoint and clear the transaction log. (See below).
  4. Once the transaction log was cleared I ran DBCC SHRINKFILE (DatabaseName_LOG) to reclaim the empty space.
  5. Finally I deleted the backup transaction log from above!
    1. In general having more than one transaction log can hurt performance. I added the additional transaction log temporarily and once it was no longer needed I removed it.
  6. USE <DatabaseName>;
    GO
    CHECKPOINT;
    GO
    CHECKPOINT; -- run twice to ensure file wrap-around
    GO

As Always thanks for looking!

Advertisements

7 thoughts on “The transaction log for database is full due to ‘REPLICATION’. “Replication not enabled.” CDC

  1. Chintu June 17, 2015 / 5:42 pm

    Awesome man, saved me hours of trouble shooting – thanks a million 🙂

  2. Ted November 10, 2015 / 4:29 pm

    This happened in Test, which was sit to simple recovery mode. All other examples were for full recovery mode. Thanks for posting this! It saved my morning.

  3. Debbie Frazer October 14, 2016 / 7:04 pm

    This happened to me today in production. Your post was the solution and saved me 🙂 My tlog file didn’t have a max specified and it grew till it consumed the entire drive. I had to create a new tlog file on a different drive. Thankfully I had another drive to utilize. Disaster averted. Thanks!!

    • Mitchell Pearson October 14, 2016 / 7:36 pm

      That’s great! Thanks for letting me know this helped you!

  4. Mebtu Abebe April 9, 2017 / 5:18 pm

    A useful post for anyone like me. My database was involved in a long running transaction which I hadn’t figured out. I hope the log was not purged due to this (in full recovery mode & scheduled log backup) and it run out of space. I got this article at a time of panic and added a new tran log file on another drive. Soon I went to the clerks, the cashiers, the doctors, … and shouted ‘Continue! It works.’ It was in a busy clinic. Great thanks!

    • Mitchell Pearson April 10, 2017 / 1:40 am

      That’s awesome, I’m really glad this post is still helping people!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s