DBA

The transaction log for database is full due to replication CDC

Advertisements

I have got an email alert differential backup is failed for one of the database. There are 80+ databases are hosted the error message from job history is not clear. I tried to run differential backup for that database and got error ‘The transaction log for database is full due to replication’.

Just checked the disk space of the log file and could see it has only 10 mb free in drive.

I just checked log_reuse_wait_desc it says Replication, I am sure that none of our SQL instances are having replication. Strange that we have some CDC been enabled and for CDC it is reporting the log_reuse_wait_desc as replication.

USE master;
GO
SELECT name, log_reuse_wait_desc, * FROM sys.databases
WHERE name = '<Database Name>';

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

Quick fix: Check the transaction log drive and try to get free space.

By moving non-database files, shrink other database log file (OR) check with Infra team and expand the drive.

Since you cannot do anything with 10 MB free space in the drive, other than pointing log files to new drive, easy fix is free will be getting some space.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

%d bloggers like this: