SQL SERVER – Huge Transaction Log in Snapshot Replication!

Learning never stops for me! I was into a situation where the log space for the database is not getting reused and it is increasing in size. As usual, I asked to check sys.databases catalog view and we found that the log_space_reuse_wait as “Replication”.

The client has confirmed that they have replication configured on the database and it was SNAPSHOT replication.

It was not making sense to me why snapshot replication would keep a hold on LDF file. I asked to run DBCC OPENTRAN command and found below

Replicated Transaction Information:

Oldest distributed LSN     : (0:0:0)

Oldest non-distributed LSN : (82:2496:1)

Again, this was not making sense as snapshot doesn’t use transaction log to generate snapshot files. I went a step further and did below mathematics.

82 decimal = 52 hex
2496 decimal = 9C0 hex
1 decimal = 1 hex

So LSN in the output is 00000052:000009c0:0001

Select [Transaction ID] from fn_dblog(NULL, NULL)
where [Current LSN] = '00000052:000009c0:0001'

Found 0000:0000052c so we looked into log again to see the transaction information.

Select * from fn_dblog(NULL, NULL)
where [Transaction ID] = '0000:0000052c'

It was some ALTER TABLE command. Found below MSDN article

How to: Replicate Schema Changes

We executed below to find details about publication. The name is “snap”

sp_helppublication 'snap'

To fix the issue, I ran below

exec sp_repldone null, null, 0,0,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.

Once above command is run, we need to run the snapshot agent again and then go to a regular procedure to shrink the T-Log file. Now, to prevent such incident in the future, I have asked to execute the following T-SQL batch to disable schema changes replication:

-- Sample Query
DECLARE @publication AS sysname
SET @publication = N'snap'

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

This was a learning for me that ALTER can cause issue with snapshot replication.

Reference: Pinal Dave (https://blog.sqlauthority.com)