Sql-server – Unexpected growth Transaction log file 100 GB which is part of Transactional Replication

replicationsql serversql server 2014transaction-logtransactional-replication

I configured transactional replication on SQL Server 2014/Windows Server 2012. The database size is only 30 MB, but log file size is 100 GB. Every day, the log file size is growing 5-7 GB.

Database transaction log file size increased abnormally. Data file size is 30MB and the transaction log file grows to 95 GB.

While database under testing mode…

Subscriber of replication is also primary replica of Always On Availability Group.

How to reduce the size of transaction log file?

I had taken log backup and in full recovery model.

SELECT name, log_reuse_wait_desc FROM sys.databases;

…returns LOG_BACKUP.

Replication is running successfully. Subscriber is receiving changes from publisher.

Best Answer

You may run below scripts to check the log reuse wait of the database(s):

SELECT DB_NAME(database_id) AS db_name, log_reuse_wait_desc FROM sys.databases;
Related Question