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”

SQL SERVER - Huge Transaction Log in Snapshot Replication! replicate-ddl

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

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

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

SQL Scripts, SQL Server, Transaction Log
Previous Post
Next Post
SQL SERVER – Identifying Database Default Locations for Data and Logs

Related Posts

6 Comments. Leave new

  • Neeraj Mittal
    April 7, 2016 8:20 am

    Thanks, Good piece of information.

  • Another way to find the Open Transactions:

    SELECT * FROM sys.sysprocesses WHERE open_tran = 1
    ORDER BY login_time

  • A good article, although old one.
    Its perfect for snapshot replication , what about Transaction Replication ?


  • Thanks for this article. We had the same issue on a very large (500+ GB) simple recovery db today. The log file was almost 500GB. It only has snapshot replication, which is weird because I would have expected transactional repl log readers to cause more issue with the log file.

    Anyway, dropping the replication freed up 99% of the log file space.

    Going to keep an eye on it and see if we need to do the sp_repldone trick at some point.

  • Gogi Turboto
    May 10, 2023 1:40 am

    Hi Paul,
    Will changing the “replicate_dll” option to 0 affect a Snapshot replication in any way? Or it is safe to do so?


Leave a ReplyCancel reply

Exit mobile version