I have a SQL servers in an agreement group, set as synchronus commit.
Due to this setup, log backups only reclaim logspace when as back as the secondary a has processed.
I noticed my log space wasn't been re used on the primary, so I looked in sys.databases and that said log reuse waiting on active transaction. Therefore I run dbcc opentran which says no open transactions. So I looked on the secondary, dbcc opentran returns a spid.
However I run dbcc inputbuffer(spid) and that say spid doesn't exist.
I look on the primary and that spid doesn't exist, in sleeping state and doesn't appear as an opentran. I kill the spid and suddenly it clears off the secondary and log space is reused.
I can someone help me understand why this happens?