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
We executed below to find details about publication. The name is “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)