Log file full due to snapshot replication

by

I would like to share a scenario where disk space was about to be full on one of critical SQL database server. Subjected disk was log disk and was holding only database log files. All the databases were in full recovery model and regular log backups were happening without any issues. However, log truncation was not happening and it was pending for ‘REPLICATION’ when we checked using below simple query:

select DB_NAME(database_id),log_reuse_wait_desc,* from sys.databases

If ‘log_reuse_wait_desc’ is ‘REPLICATION’ , then we need to check type of replication configured on truncation stuck databases before deciding next course of action:

  1. If database is configured with transaction replication then we need to check if there are any issues with log reader agent.
  2. If database is configured with snapshot replication which was the case in issue we faced, then we can fix it in below manner:

a) check using below command if it is snapshot replication

use DB_NAME
sp_helppublication 'DB_NAME'

b) Validate if publication property [replicate_ddl]=1

Ideally, snapshot replication should not restrict database log truncation, however it could happen if [replicate_ddl] is set to 1

Long Term Fix: Execute the following T-SQL batch to disable schema changes replication (if type is SNAPSHOT):

DECLARE @publication AS sysname
SET @publication = N'Publication Name'

-- Turn off DDL replication for the publication.
USE 'DB_NAME'
EXEC sp_changepublication
@publication = @publication,
@property = N'replicate_ddl',
@value = 0
GO

Short Term Fix: Run below command to marks _all_ transactions as distributed.

EXEC sp_repldone @xactid = NULL, @xact_segno = NULL, @numtrans = 0, @time = 0, @reset = 1

NOTE: Above command is safe in snapshot replication because it marks _all_ transactions as distributed. This would cause problems in another type of replications.

Run log backup again and check log would be ready for truncation. You can shrink further if required.

–Re-check log reuse wait column

select DB_NAME(database_id),log_reuse_wait_desc,* from sys.databases

‘log_reuse_wait_desc’ should be ‘NOTHING’ now and then we can shrink the log file to make more available space for the drive.

I hope it helps you. Please add if you observe any related scenarios or if you see something to be added more in this article.

Post Views: 388