REPLICATION preventing log reuse but no replication configured

Last week, for the second time in as many weeks, I was sent a question in email from someone who had a transaction log that was growing out of control. He’d already queried log_reuse_wait_desc for the database (see this post for some more background) and the result was REPLICATION.

The problem was, there was no replication configured for that database. Just for completeness, he tried turning off the publish and merge publish settings for the database with sp_replicationdboption, ran sp_removedbreplication on the database, and then when those didn’t work, he also tried configuring and then removing replication. Nothing worked and the transaction log kept growing.

The problem turned out to be Change Data Capture. CDC uses the replication log scanning mechanism to harvest changes from the database, either piggy-backing on replication’s Log Reader Agent job or creating it’s own capture job if replication isn’t configured. If CDC is configured but the capture job isn’t running, the log_reuse_wait_desc will show as REPLICATION, as the log manager doesn’t have any way to know *why* the replication log scanner is configured, just that it is, and it hasn’t run.

So, if you ever see REPLICATION as the log_reuse_wait_desc and don’t have replication configured, check the is_cdc_enabled flag in sys.databases too. And then either figure out why the CDC capture job isn’t running correctly (see Change Data Capture Agent Jobs in this BOL entry), or remove CDC if it’s not supposed to be there (see this BOL entry).

Hope that helps a few people!

19 thoughts on “REPLICATION preventing log reuse but no replication configured

  1. Yep.

    What I also like is that some software enables CDC under the covers and does away with those built-in jobs; like Oracle GoldenGate. Everything works fine until their agent stops running and suddenly nobody knows what’s going on.

  2. We had seen something similar last year.. At first we thought it was replication and then found the change data capture, the capture job was not running on the server. The database was mirrored but app team forgot to create the capture job on both mirror partners.

  3. Other scenario which we saw, the replication was configured b/w Oracle(something called golden gate) to SQL database objects where in the SQL level -it says none configured as replication and No databasez involved, it was based on oracle

  4. I’ve got a new one (to me anyway). I’m well acquainted with CDC preventing log truncation but this time I’ve got a database (on a server that also has two databases configured for CDC). Simple recovery, no replication on the server, and is_cdc_enabled is zero (no evidence of cdc schema tables or cdc Agent jobs for this db). And still…log full and growing, and log_reuse_wait_desc is REPLICATION. It’s a non-prod system so I’ve taken it offline and back online, jiggled with the recovery model, and still, no change.

    Very strange! If not replication, and not CDC, I wonder what it could be?

    1. Never mind, sussed it out…while replication was not actively configured on the server, as I understand it it may have been a restore from an instance where replication was configured, and so the following cleared the log:

      EXEC sp_removedbreplication ‘databasename’

      This was on 10.5, in case anyone else runs into this issue in the future.

      1. So I just ran into this also. Database was restored (over 2 months ago!) from a server that had replication configured, though this particular database was never involved in a publication. All of a sudden at 2:16 am PDT this morning I started getting flooded with Severity 020 errors (The log scan number (5423817:3214:0) passed to log scan in database ” 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.)
        I’m thinking, replication isn’t even setup on this server, CDC is not ENABLED on the DB. Did the Enable replication/Create publication thing and that cleared it up then removed both.
        I’m just not clear and would like to know what started it thinking it was part of replication all of a sudden. Unfortunately Windows and SQL event logs got so bloated I can’t even open them to see what occured at that time.

      2. Thanks. This solved it for me. After i executed EXEC sp_removedbreplication ‘databasename’, log_reuse_wait_desc changed to ACTIVE TRANSACTION. With that i was able to shrink log file.

  5. I had the exact same scenario as Nic, database showing waiting on Replication but neither replication nor change data capture was enabled (also same release 10.5). However, sp_removedbreplication did not work! Other peoples comments about 3 rd party tools turning it on and not telling you caused me to review a test server I had used to recover this database from a CHECKDB error using Redgate’s SQL Data Compare.
    Sure enough, that instance was also showing replication in sys.databases entry but nothing was turned on. I enabled ‘Replication XPs’ and then had to recycle SQL to finish the configuration, changed it back, recycled SQL and abra-cadabra the sys.databases log_reuse_wait_desc showed ‘NOTHING’ instead of ‘REPLICATION’. Problem solved!

    1. We had a copy of a production db that we restored on a new server for pre-migration buildout of our application, testing and such.
      the db typically uses CDC but it was restored in this instance without the keep_cdc option as it wasnt actively needed until the live migration. The db was also in simple recovery. log_reuse_wait_desc showed REPLICATION and the log was full.

      confirmed the is_cdc_enabled value was 0 and ran sp_removedbreplication as noted above and still could not truncate the log until SQL was stopped and restarted.

      Thanks for everyone’s comments they were very helpful!

        1. Thanks for that, I had the same trouble as @CJ Morgan with my errorlog bloated with errors like

          The log scan number (50699:106386:0) passed to log scan in database ”

          After doing sp_replicationdboption and sp_removedbreplication, the database was still showing log_reuse_wait_desc REPLICATION which was annoying.

          After running a CHECKPOINT now it’s alright, but since the log was cleared, the log chain is now broken and need new full backup before new log backups can happen.

          Cheers.

  6. great one Paul. just ran into this, and I remembered I saw something about it in the newsletters. thanks

  7. So I had this issue on a replicated database, cdc not enabled. Log had grown to over 700 GB, simple recovery and all VLF’s active so could not clear. Could not find any long running active txns. Took db offline to rollback anything and put back online, didnt help. Turned out the transactional replication had some how lost the subscription. So what I did is drop the publication and remove the db from replication. I was then able to clear and hence shrink the log. I then added back the replication components and it has remained stable.

  8. in my case i removed replication and was able to backup log. I didnt have to execute checkpoint manually. Thanks all who suggested this quick fix.

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.