Open transaction preventing log reuse?

by BeginnerDBADan   Last Updated December 06, 2018 15:06 PM

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?



Related Questions


Updated December 11, 2018 22:06 PM

Updated March 28, 2018 17:06 PM

Updated April 04, 2016 09:02 AM

Updated February 18, 2016 05:02 AM

Updated April 08, 2016 08:02 AM